Is there Dax code to take the difference between 2 rows from a summarized table

Question

I need to be able to get the difference between 2 successive rows in a Summarized table, by rank, so that I can then get the average of the differences of each row. And I can't create a new table as I need this DAX query to be filterable

I've been able to get this far, but do not know how to add a difference column that will show the DSOValue difference between rows 1-2, 2-3, 3-4 ...

ADDCOLUMNS (
    SUMMARIZE (
        Table1,
        Table1[Date],
        "DSOValue", DIVIDE ( SUM ( 'Table1'[AR] ) * 91.5, SUM ( 'Table1'[Sales] ), 0 )
    ),
    "Rank", RANKX (
        Table1,
        CALCULATE (
            COUNTROWS ( Table1),
            FILTER ( Table1, Table1[Date] <= EARLIER ( Table1[Date] ) )
        ),,ASC,DENSE)
)

I've tried embedding this code within another ADDCOLUMNS function, but it won't let me CALCULATE and FILTER on my created columns (DSOValue and RANK)

Aldert Aldert · Accepted Answer · 2019-05-30T17:19:09

you can use the following:

Diff = 'Table1'[ DSOValue ] - LOOKUPVALUE('Table1'[ DSOValue ]; 'Table1'[Date     ];CALCULATE( MAX('Table1'[Date     ]);FILTER('Table1';'Table1'[Date     ]<EARLIER('Table1'[Date     ]))))

Note: You cannot use <= here, it will pick its own date and all will be null. It would also be easier to add an index column, when you have this you can use Lookupvalue with Index -1

Is there Dax code to take the difference between 2 rows from a summarized table

1 Answers