Previous Month calculations

Cedric · September 25, 2023, 5:02pm

Hello Everyone,

I have a request that I am not really sure how to adress.

The attached file shows a Matrix with different rows (, , ) by month.

My goal is for the ‘grand total’ of the previous month to become the ‘qtty inventory’ of the following month.

So for example, 377,73 beeing the total of january, it would become my inventory qtty for february.

Any advice on how to achieve this ?

Thank you very much,

Cédric
Sept 2023.pbix (242.9 KB)

Keith · September 25, 2023, 5:38pm

I’m a little confused on what you are saying here.

So what you are saying here the ending January total is the beginning total for February. Am I correct?

thanks
Keith

Cedric · September 25, 2023, 5:59pm

Hello Keith,
thank you for taking the time to review.
Indeed, my january ‘‘total’’ becomes my ‘‘inventory’’ for february.
Then, my february ‘‘total’’ becomes my inventory for march.
Sorry for the confusion. Please refer to the attached pic. the pbix was updated as well.
Sept 2023.pbix (276.6 KB)

Keith · September 25, 2023, 6:04pm

in financial terms ending inventory becomes begining inventory for the next month

Cedric · September 25, 2023, 8:45pm

Yes
thank you.
I’ve tried different approaches, with DATEADD Or PreviousMonth but I do not get what I need.

Thanks

Keith · September 25, 2023, 10:14pm

did you search youtube? or even ask Chatgpt?

There are videos within EDNA Inventory management power bi showcase

Harsh · September 26, 2023, 4:12pm

Hello @Cedric,

Thank You for posting your query onto the Forum.

In order to achieve the solution based on the scenario that you’ve mentioned. Below are the measures alongwith the screenshot of the final results provided for the reference -

1. Opening Inventaire = 


    ----- Calculation of Opening Inventory Ever -----


VAR _First_Inventory_Date_Ever = 
CALCULATE( MIN( Tout[Date calculé pour rapport inventaire] ) , 
    REMOVEFILTERS( ) )


VAR _Min_Date = 
MIN( Dates[Date] )

VAR _Max_Balance_Date = 
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            Tout , 
            Tout[Type du produit] ) , 
            "@Max_Balance_Date" , 
            CALCULATE( MAX( Tout[Date calculé pour rapport inventaire] ) ) ) , 
        Dates[Date] <= _Min_Date ,
        Tout[Type du produit] = "Inventaire" )

VAR _Max_Balance_Dates_With_Lineage = 
TREATAS( 
    _Max_Balance_Date , 
    Tout[Type du produit] , 
    Dates[Date] )

VAR _Opening_Inventory_Ever = 
CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
    _Max_Balance_Dates_With_Lineage )


    ----- Calculation of Previous Month Cumulative Entrant -----


VAR _Previous_Month_Cumulative_Entrant = 
CALCULATE(
    CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
        DATEADD( Dates[Date] , -1 , MONTH ) , 
        Tout[Type du produit] = "Entrant" ) , 
    FILTER( ALLSELECTED( Dates[Date] ) , 
        Dates[Date] <= MAX( Dates[Date] ) ) )


    ----- Calculation of Previous Month Cumulative Entrant -----


VAR _Previous_Month_Cumulative_Sortant = 
CALCULATE(
    CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
        DATEADD( Dates[Date] , -1 , MONTH ) , 
        Tout[Type du produit] = "Sortant" ) , 
    FILTER( ALLSELECTED( Dates[Date] ) , 
        Dates[Date] <= MAX( Dates[Date] ) ) )


    ----- Calculation of Final Results -----


VAR _Results = 
SWITCH( TRUE() , 
    ISINSCOPE( Dates[MM-YYYY] ) && _First_Inventory_Date_Ever = _Min_Date , _Opening_Inventory_Ever ,
    ISINSCOPE( Dates[MM-YYYY] ) , _Opening_Inventory_Ever + _Previous_Month_Cumulative_Entrant + _Previous_Month_Cumulative_Sortant )



RETURN
_Results

2. Entrant = 
IF( 
    ISINSCOPE( Dates[MM-YYYY] ) , 
    CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
        KEEPFILTERS( Tout[Type du produit] = "Entrant" ) ) )

3. Sortant = 
IF(
    ISINSCOPE( Dates[MM-YYYY] ) , 
    CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
        KEEPFILTERS( Tout[Type du produit] = "Sortant" ) ) )

4. Closing Inventaire = 


    ----- Calculation of Opening Inventory Ever -----


VAR _Min_Date = 
MIN( Dates[Date] )

VAR _Max_Balance_Date = 
CALCULATETABLE(
    ADDCOLUMNS(
        SUMMARIZE(
            Tout , 
            Tout[Type du produit] ) , 
            "@Max_Balance_Date" , 
            CALCULATE( MAX( Tout[Date calculé pour rapport inventaire] ) ) ) , 
        Dates[Date] <= _Min_Date ,
        Tout[Type du produit] = "Inventaire" )

VAR _Max_Balance_Dates_With_Lineage = 
TREATAS( 
    _Max_Balance_Date , 
    Tout[Type du produit] , 
    Dates[Date] )

VAR _Opening_Inventory_Ever = 
CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
    _Max_Balance_Dates_With_Lineage )


    ----- Calculation of Cumulative Entrant -----


VAR _Cumulative_Entrant = 
CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
    Tout[Type du produit] = "Entrant" , 
    FILTER( ALLSELECTED( Dates[Date] ) , 
        Dates[Date] <= MAX( Dates[Date] ) ) )


    ----- Calculation of Cumulative Sortant -----


VAR _Cumulative_Sortant = 
CALCULATE( SUM( Tout[Quantité calculé pour inventaire] ) , 
    Tout[Type du produit] = "Sortant" , 
    FILTER( ALLSELECTED( Dates[Date] ) , 
        Dates[Date] <= MAX( Dates[Date] ) ) )


    ----- Calculation of Results -----

VAR _Results = 
IF(
    ISINSCOPE( Dates[MM-YYYY] ) , 
    _Opening_Inventory_Ever + _Cumulative_Entrant + _Cumulative_Sortant )



RETURN
_Results

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this helpful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Inventory Calculation - Harsh.pbix (236.4 KB)

Astyork · April 16, 2024, 12:01am

When conducting previous month calculations, gather accurate data from the preceding month to analyze trends, performance, and progress. Utilize this information to make informed decisions and plan effectively for the future.