topic Re: Difference in amount since last report date made automatic in DAX Commands and Tips

Difference in amount since last report date made automatic

jksl_12 — Tue, 05 Dec 2023 12:50:16 GMT

Hey there

I have run into a problem with the automated report that I am setting up and I am hoping that someone will be able to point me in the right direction. It is the last thing I am missing, and I would really enjoy if it was possible to include it.

Below I have an example of my data that is put into the table.

Region	Date of report	Category	Storage amount
North	December 1st	Pencils	4.500
South	December 1st	Pencils	6.000
West	December 1st	Pencils	7.500
Central	December 1st	Pencils	10.500
North	November 1st	Pencils	5.000
South	November 1st	Pencils	5.000
West	November 1st	Pencils	8.000
Central	November 1st	Pencils	12.000

I would love to be able to set up a formula so that I can see the difference from last month. However, the thing that I would enjoy even more is for this to happen automatically when I refresh my data. On January 1st I am going to be getting new data put into the same table, so I would want it to be compared to the December data instead and see the difference between the values.

So basically what I would want the finished matrix to look like this, and then be able to update it without having to do that manually. So that means next time the change would be calcuated from December 1st and then compared with the new values of January 1st.

Region	Storage amount	Change since last
North	4.500	-500
South	6.000	1.000
West	7.500	-500
Central	10.500	-1.500

I hope someone will be able to help me out, and if there is anything else you need in order to be able to solve my problem, then do let me know!

Thanks in advance and kind regards

- J

Re: Difference in amount since last report date made automatic

Anonymous — Tue, 05 Dec 2023 15:16:17 GMT

Assuming that it's refreshed monthly and you have no calendar table

Change since last refresh =

VAR PreviousMonthValue =

CALCULATE(

SUM('YourTableName'[Storage Amount]),

'YourTableName'[Date of Report] = EDATE(MAX(YourTableName'[Date of Report]), -1)

)

VAR CurrentMonthValue =

CALCULATE(

SUM('YourTableName'[Storage Amount]),

'YourTableName'[Date of Report] = MAX(YourTableName'[Date of Report])

)
RETURN
CurrentMonthValue - PreviousMonthValue

If it's refreshed randomly, you might need to create a 'Last Refreshed' date column in Power Query, and then use the datesbetween function in place of EDATE/Max

Re: Difference in amount since last report date made automatic

ray_codex — Tue, 05 Dec 2023 16:06:46 GMT

Assuming you have a date dimension connected (that is set as a date table), you can use DAX time intelligence to compare this. U can slice the current month and use this as a DAX statement:

difference since last month = SUM ( Yourtable[Storage amount] ) - CALCULATE ( SUM ( Yourtable[Storage amount] ), PREVIOUSMONTH ( YourDateTable[Date] ) )

This will substract the previous month amount from the month you filtered, or used as a column/row.