Calculate a difference for values of max date with previous date

[TheHans]

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

[CNENFRNL]

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 )

[TheHans]

Hi CNENFRNL,

thanks a lot. Appreciate your support.

H

[smpa01 · Accepted solution]

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

[TheHans]

Hi smpa01,

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

H