Using existing data in one column to calculate a delta value in another column with DAX

[Murten]

Hi there

So I have a dataset with 2 columns: "Timestamp" and "NumericValue"

My objective is to create a new column which uses DAX to calculate the change in NumericValue. It should obviously start off with the earliest timestamp date and calculate based off previous value in NumericValue. As new numbers are added continously the logic should be generic and work automatically.
To give context i want to calculate the change of water consumption coming from an IoT water sensor only providing the meter (accumulated total consumption)


My table "Measurements" with examples:
| Timestamp    | NumericValue | Delta_Value |
|--------------- |------------------|---------------|
| 2024-01-01   | 0                        | 0                   |
| 2024-01-02   | 10                      | 10                 |
| 2024-01-03   | 15                      | 5                   |
| 2024-01-04   | 16                      | 1                   |
etc etc

I have tried a lot including chatgpt suggestions, but it doesn't really work. I made with work in Javascript, but i really want it to work using DAX
Thank you!

[Murten]

 -

[Murten]

-

[Murten]

Hi there

Thank you for the suggestion. However i get this error (translated); "Even if MatchBy columns are specified, duplicate rows are detected in the Relation parameter of OFFSET. This is not allowed."
- There are obviously duplicates in the NumericValue column, but that happens when the sensor doesn't register any new consumption = meter is not moving/counting

OK i may know what the issue is, but now how to fix it... 

So i guess timestamp will have duplicates as I also have columns "deviceId" and "field" with 4 different but constant values, where each value has their own row of timestamp, value etc... So 4x same timestamp
- Yes, this is coming from a NoSQL db.... 
So maybe I need to also filter by only one of the four variables somehow?

This is my code based on your suggestion:

Delta_heat =

VAR _previousvalue =

    SUMX (

        OFFSET (

            -1,

            Measurements,

            ORDERBY ( Measurements[timestamp], ASC ),

            ,

            ,

            MATCHBY ( Measurements[timestamp] )

        ),

        Measurements[numericValue]

    )

RETURN

    IF ( NOT ISBLANK ( _previousvalue ), Measurements[numericValue] - _previousvalue, 0 )

[Jihwan_Kim]

Hi, 
I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file, that is for creating a calculated column.

Delta_value CC =
VAR _previousvalue =
    SUMX (
        OFFSET (
            -1,
            Data,
            ORDERBY ( Data[Timestamp], ASC ),
            ,
            ,
            MATCHBY ( Data[Timestamp] )
        ),
        Data[NumericValue]
    )
RETURN
    IF ( NOT ISBLANK ( _previousvalue ), Data[NumericValue] - _previousvalue, 0 )