Subtract todays total from previous days total

[Anonymous]

Hi all! I am trying to create a column where it takes the dates total_installed and subtracts the previous dates total_installed.

My table 'DATA' looks like this:

DATE              Total_installed
20/11/2023    68900
19/11/2023    68896
18/11/2023    68893
17/11/2023    68738
16/11/2023    68556
15/11/2023    68340
14/11/2023    68099
13/11/2023    67897

The additional column would look like this:

DATE              Total_installed    New_installs
20/11/2023    68900                 4
19/11/2023    68896                 3
18/11/2023    68893                 155
17/11/2023    68738                 182
16/11/2023    68556                 216
15/11/2023    68340                 241
14/11/2023    68099                 202
13/11/2023    67897                 0

Obviously easy to do in Excel or SQL but new to DAX and it is a bit beyond me. Appreciate any suggestions or solutions. 

I have found similar questions but I am unable to reverse engineer the formula to work with my own data. Thanks all!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column.

OFFSET function (DAX) - DAX | Microsoft Learn

New Installs CC =
VAR _previous =
    MAXX (
        OFFSET (
            -1,
            SUMMARIZE ( Data, Data[DATE], Data[Total_installed] ),
            ORDERBY ( Data[DATE], ASC )
        ),
        Data[Total_installed]
    )
RETURN
    IF ( NOT ISBLANK ( _previous ), Data[Total_installed] - _previous, 0 )

[Anonymous]

Absolute legend. Works perfectly and the formula actually makes sense, which is the most imporant part. Thank you 🙂