topic Re: Help！ How to get the data from previous month in DAX Commands and Tips

Help！ How to get the data from previous month

NBU_FFF — Sat, 20 Jan 2024 05:09:01 GMT

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

Re: Help！ How to get the data from previous month

Fowmy — Sat, 20 Jan 2024 06:00:29 GMT

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.

Re: Help！ How to get the data from previous month

NBU_FFF — Sat, 20 Jan 2024 06:29:38 GMT

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.

Re: Help！ How to get the data from previous month

Fowmy — Sat, 20 Jan 2024 06:44:32 GMT

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])

Re: Help！ How to get the data from previous month

NBU_FFF — Sat, 20 Jan 2024 10:39:39 GMT

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

Re: Help！ How to get the data from previous month

Fowmy — Sat, 20 Jan 2024 11:24:39 GMT

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.

Re: Help！ How to get the data from previous month

NBU_FFF — Sat, 20 Jan 2024 11:56:45 GMT

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.

Re: Help！ How to get the data from previous month

Fowmy — Sun, 21 Jan 2024 20:00:04 GMT

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

Re: Help！ How to get the data from previous month

NBU_FFF — Mon, 22 Jan 2024 14:26:09 GMT

Fowmy Great Solution. Thank for Help!