Help！ How to get the data from previous month

[NBU_FFF]

Dear Community, I'm struggled to get the data from previouis month in Power BI . My case is we need to get 202401 fianl qty as the input of 202402 ini qty.  How to give DAX to show 9 & 19 as below red part in Power BI Matrix table?  Greatly appreciated!

Location	202401	202402
	Ini Qty	In Qty	Out Qty	Final Qty	Ini Qty	In Qty	Out Qty	Final Qty
AA	10	1	2	9	9
BB	20	2	3	19	19

[Fowmy]

@NBU_FFF 

If you have setup your model with a calendar table, then, there are multiple ways to get the previous month using functions like PREVIOUSMONTH and DATEADD. 

Can you share some sample data with the desired output to have a clear understanding of your question?
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

[NBU_FFF]

Thanks for the quick reply.

My data table as below. I can not have my head around how to put Month_end data as Month_ini data for each WH.

Appreciate the support.

[Fowmy]

@NBU_FFF 

I added a new column to arrive at the Month End value by each WH:

Initial Value = 

VAR __WH = Table27[WH]
VAR __Month = Table27[Month]
VAR __Result = 
    MAXX(
        FILTER(
            Table27,
            Table27[WH] = __WH && Table27[Month] < __Month 
        ),
        Table27[Month_end]
    )
RETURN
    COALESCE(__Result,Table27[WH_Ini])

[NBU_FFF]

@Fowmy  A big thanks. The formual is working. But in my real case, the physical existing columns are "In" and "Out". "Initial Value"  and "Month_end" columns should be added by DAX,

"Initial Value" should be from previous month "Month_end" data

the "Month_end" should be calculated by "Initial value" + "In" - "Out".

The desired output should be like sample below:

So in this secnario, how to set DAX of "initial value" and "Month_end" correctly?

I also attached my sample PBIX file for your updating.

https://1drv.ms/u/s!AhkI23dIWO7yiUo2qB9womv65ZDB?e=O3dYD6

[Fowmy]

@NBU_FFF 

You cannot calculate when there is recurrsion, meaning both columns cannot depend on each other. Reagrding the value that you have shared for Initial Value, how did you get 215 and 217 when the repective ending balance was 115 and 217 in the previous month. Also before you these transactions shown, you should have a balance of 110 and 212 as the begining balance.

[NBU_FFF]

@Fowmy  Very sorry for the typo. Just sticking on that. I've corrected it as should be:

If you think it is necessary to add other column pls advise or update on my shared file. 

https://1drv.ms/u/s!AhkI23dIWO7yiUo2qB9womv65ZDB?e=lpkS4R

Thanks again.

[Fowmy · Accepted solution]

@NBU_FFF 

Here is the solution:

Initial Value = 
VAR __WH = 'Table'[WH]
VAR __Month = 'Table'[Month]
VAR __T = 
    FILTER(
    'Table',
        'Table'[WH] = __WH && 'Table'[Month] < __Month 
    )
VAR __Result = SUMX( __T ,'Table'[In] - 'Table'[Out] )

RETURN
    __Result

Month End = 
VAR __WH = 'Table'[WH]
VAR __Month = 'Table'[Month]
VAR __T = 
    FILTER(
    'Table',
        'Table'[WH] = __WH && 'Table'[Month] <= __Month 
    )
VAR __Result = SUMX( __T ,'Table'[In] - 'Table'[Out] )

RETURN
    __Result

[NBU_FFF]

@Fowmy  Great Solution. Thank for Help!