Calculate expression by previous row

JJ_masgio — Wed, 23 Aug 2023 15:28:03 GMT

Hi!
Quite new to DAX.
This is my fact table:

Date (dd-mm-yyyy)	Value
03-06-2023	987
23-06-2023	1112
02-07-2023	1234
12-07-2023	1543

I'm interested in the delta Value across months... but the measure I'm interested in is sush as:
Delta[07] = 1543 - 1112
I mean is the difference between the MAX Value of month 07 and the MAX Value of month 06.
Of course I have a complete Calendar table.
Can you help me in filtering?

Thanks a lot!

Re: Calculate expression by previous row

Anonymous — Mon, 28 Aug 2023 05:53:32 GMT

Hi JJ_masgio ,

I created a sample pbix file(see the attachment), please check if that is what you want.

Measure = VAR _selyear = SELECTEDVALUE ( 'Calendar'[Date].[Year] ) VAR _selmonth = SELECTEDVALUE ( 'Calendar'[Date].[MonthNo] ) VAR _curmvalue = CALCULATE ( MAX ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Date] ) = _selyear && MONTH ( 'Table'[Date] ) = _selmonth ) ) VAR _premdate = EOMONTH ( DATE ( _selyear, _selmonth, 1 ), -1 ) VAR _premvalue = CALCULATE ( MAX ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), YEAR ( 'Table'[Date] ) = YEAR ( _premdate ) && MONTH ( 'Table'[Date] ) = MONTH ( _premdate ) ) ) RETURN IF ( ISBLANK ( _premvalue ) || ISBLANK ( _curmvalue ), BLANK (), _curmvalue - _premvalue )

Delta = IF ( ISINSCOPE ( 'Calendar'[Date].[Year] ), [Measure], SUMX ( GROUPBY ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ), [Measure] ) )

Best Regards

topic Re: Calculate expression by previous row in DAX Commands and Tips

Calculate expression by previous row

Re: Calculate expression by previous row