Calculate the difference between the current and the previous row in DAX

Question

In Analysis Services I have a table for Covid Cases, as shown below:

It shows the cumulative cases on a daily basis for 193 different countries. I would like to add a calculated column to calculate the difference between the current row and the previous row, so I can see the daily new cases. Also, as column Country/Region contains 193 different countries, this calculation needs to be somehow grouped for each country. The Date column should also be in the right order.

How should I sort the table and what DAX function should I use to make this work?

Answer 1

Try this

Column =
    VAR current =
        CALCULATE ( MAX ( tbl[Value] ) )
    VAR prev =
        MAXX (
            FILTER (
                tbl,
                tbl[Country] = EARLIER ( tbl[Country] )
                    && tbl[Date] < EARLIER ( tbl[Date] )
            ),
            tbl[Value]
        )
    RETURN
        current - prev