topic Re: Using existing data in one column to calculate a delta value in another column with DAX in DAX Commands and Tips

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

Murten — Mon, 22 Jan 2024 14:54:11 GMT

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!

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

Jihwan_Kim — Mon, 22 Jan 2024 16:24:14 GMT

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 )

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

Murten — Tue, 23 Jan 2024 08:58:22 GMT

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 )

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

Murten — Tue, 23 Jan 2024 08:59:19 GMT

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

Murten — Tue, 23 Jan 2024 08:59:04 GMT