topic Re: Calculate a difference for values of max date with previous date in DAX Commands and Tips

Calculate a difference for values of max date with previous date

TheHans — Fri, 26 Nov 2021 13:11:37 GMT

Hi,

I have a table with IDs, dates and values per ID-Date combination. I would like to calculate the difference of the values compared to the previous date for the same ID.

For example: ID 1 increased from 01.11.2021 to 02.11.2021 by 5. So the result is 5.
If an ID was not there on the previous date, the value would be null.
For example: ID 4 came in on 03.11.2021 with the value of 10. The diff prev. date is null since there is no previous date with this ID.

How do I calculate this new "Diff prev. date" column using DAX`?

Thanks in advance.

ID	Date	Value	Diff prev. date
1	01.11.2021	10	null
2	01.11.2021	10	null
3	01.11.2021	10	null
1	02.11.2021	15	5
2	02.11.2021	8	-2
3	02.11.2021	10	0
1	03.11.2021	15	0
2	03.11.2021	11	3
3	03.11.2021	10	0
4	03.11.2021	10	null

Re: Calculate a difference for values of max date with previous date

smpa01 — Fri, 26 Nov 2021 14:18:44 GMT

TheHans you can use a measure like this

VAR _sum = SUM ( 'Table 1'[Value] ) VAR _id = MAX ( 'Table 1'[ID] ) VAR _date = MAX ( 'Table 1'[Date] ) VAR _immediatelyPrecedingSum = CALCULATE ( SUM ( 'Table 1'[Value] ), FILTER ( ALL ( 'Table 1' ), 'Table 1'[Date] = CALCULATE ( MAX ( 'Table 1'[Date] ), 'Table 1'[Date] < _date, ALLEXCEPT ( 'Table 1', 'Table 1'[ID] ) ) && 'Table 1'[ID] = _id ) ) RETURN IF ( _immediatelyPrecedingSum = BLANK (), 0, _sum - _immediatelyPrecedingSum )

If you need a calulated column

Column = VAR _sum = CALCULATE ( SUM ( tbl[Value] ) ) VAR _immediatelyPreceding = MAXX ( FILTER ( tbl, tbl[ID] = EARLIER ( tbl[ID] ) && tbl[Date] < EARLIER ( tbl[Date] ) ), tbl[Date] ) VAR _imediatelyPrecedingSum = SUMX ( FILTER ( tbl, tbl[ID] = EARLIER ( tbl[ID] ) && tbl[Date] = _immediatelyPreceding ), tbl[Value] ) RETURN IF ( _imediatelyPrecedingSum = BLANK (), 0, _sum - _imediatelyPrecedingSum )

Re: Calculate a difference for values of max date with previous date

CNENFRNL — Sat, 27 Nov 2021 01:39:24 GMT

Diff_CC = VAR __prev = MAXX( TOPN( 1, FILTER( Table1, Table1[ID] = EARLIER( Table1[ID] ) && Table1[Date] < EARLIER( Table1[Date] ) ), Table1[Date] ), Table1[Value] ) RETURN IF( NOT ISBLANK( __prev ), Table1[Value] - __prev )

Diff = VAR __prev = MAXX( TOPN( 1, CALCULATETABLE( Table1, Table1[Date] < MAX( Table1[Date] ), ALLEXCEPT( Table1, Table1[ID] ) ), Table1[Date] ), [Total] ) RETURN IF( NOT ISBLANK( __prev ), [Total] - __prev )

Re: Calculate a difference for values of max date with previous date

TheHans — Sat, 27 Nov 2021 07:04:38 GMT

Hi CNENFRNL,

thanks a lot. Appreciate your support.

Re: Calculate a difference for values of max date with previous date

TheHans — Sat, 27 Nov 2021 07:05:55 GMT

Hi smpa01,

thanks for the solution. Especially for both ways, as measure and calculated column.