Using existing data in one column to calculate a delta value in another column with DAX
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!
DAX Delta Issue - Difference Between Dates Not Working When Amount is Zero for Maximum Date Selected
I am trying to accurately calculate the difference in the amount sold between the most recent date selected and the oldest one. I used a DAX formula to create my Delta, and when calculating the difference between dates, my Delta works for most scenarios. However, there are cases where the measure is not working, specifically when we have 0 for the maximum date and a positive value > 0 for the minimum date. Here is the DAX I am using for my Delta, in my Measure: Diff = IF ( HASONEVALUE ( Table[Snapshot_Date] ), ( ( SUM(Table[Orders]) ) ), CALCULATE ( ( SUM(Table[Orders])), FILTER ( Table, Table[Snapshot_Date] = MIN ( ( Table[Snapshot_Date] ) ) ) ) - CALCULATE ( ( SUM(Table[Orders])), FILTER ( Table, Table[Snapshot_Date] = MAX ( ( Table[Snapshot_Date] ) ) ) ) ) The screenshot below shows that my Delta works perfectly when calculating the difference for the selected dates and rows below. Here follows one example where my measure is not working, where I have 0 for the maximum date and a positive value > 0 for the minimum date: For the highlighted row, the delta should be -0.1 instead. All other rows in this matrix are making sense. Can someone please help me out with this issue? Thank you 🐾
