Forum Discussion
Advocate IICalculated Column for delta calculation with previous row (lead/lag)
Hello Power BI Community!
I'm struggling with this (probably quite simple 😉 ) DAX problem:
In SQL this would be rather easy to solve using a window function with lead/lag but in DAX... I can't solve it 😞
I created a data sample (see table below) to illustrate my problem:
Basically I need to group by kpi_id and notes, order by entry date and subtract the actual values to get the delta (Red).
I tried using earlier and got to some point where it would sum up the actuals by kpi_id and notes but thats not what i need 🙂
Thank you so much and have a great weekend!
- Frank
| kpi_id | Notes | Actual | entry_date | Delta |
| AAA | Samson | 100 | 31.01.2019 00:00 | |
| AAA | Samson | 120 | 28.02.2019 00:00 | 20 |
| AAA | Peter | 40 | 31.03.2019 00:00 | |
| AAA | Peter | 45 | 30.04.2019 00:00 | 5 |
| AAA | Peter | 48 | 31.05.2019 00:00 | 3 |
| AAA | Peter | 50 | 30.06.2019 00:00 | 2 |
| AAA | Peter | 55 | 31.08.2019 00:00 | 5 |
| AAA | Samson | 150 | 30.09.2019 00:00 | 30 |
| AAA | Samson | 200 | 31.10.2019 00:00 | 50 |
| AAA | Samson | 250 | 31.01.2020 00:00 | 50 |
| AAA | Samson | 291 | 29.02.2020 00:00 | 41 |
| BBB | Elmo | 5 | 29.02.2020 12:00 | |
| BBB | Elmo | 7 | 31.03.2020 12:00 | 2 |
| AAA | Samson | 295 | 30.04.2020 10:22 | 4 |
| BBB | Elmo | 10 | 30.04.2020 12:00 | 3 |
| BBB | Elmo | 16 | 31.05.2020 12:00 | 6 |
| AAA | Samson | 350 | 30.06.2020 09:42 | 55 |
| BBB | Elmo | 20 | 30.06.2020 12:00 | 4 |
| BBB | Elmo | 25 | 31.07.2020 12:00 | 5 |
| AAA | Peter | 60 | 31.07.2020 12:12 | 5 |
| AAA | Peter | 58 | 31.08.2020 10:17 | -2 |
| BBB | Elmo | 28 | 31.08.2020 16:08 | 3 |
| AAA | Samson | 320 | 30.09.2020 14:22 | -30 |
| BBB | Elmo | 35 | 30.09.2020 16:07 | 7 |
| AAA | Peter | 70 | 31.10.2020 07:53 | 12 |
| BBB | Elmo | 40 | 31.10.2020 17:06 | 5 |
| BBB | Elmo | 45 | 30.11.2020 14:45 | 5 |
| BBB | Elmo | 38 | 31.12.2020 09:39 | -7 |
| AAA | Samson | 350 | 31.12.2020 10:39 | 30 |
| BBB | Elmo | 28 | 01.01.2021 10:59 | -4 |
| AAA | Peter | 90 | 31.01.2021 16:11 | 20 |
ferzfeld add new column using following expression:
Delta New = VAR __currentDate = Delta[entry_date] VAR __filter = ALLEXCEPT ( Delta, Delta[kpi_id],Delta[Notes] ) VAR __previousDate = CALCULATE ( MAX ( Delta[entry_date] ), __filter, Delta[entry_date] < __currentDate ) VAR __previousSales = CALCULATE ( MAX ( Delta[Actual] ), __filter, Delta[entry_date] = __previousDate ) RETURN IF ( NOT ISBLANK ( __previousSales ), Delta[Actual] - __previousSales )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
2 Replies
- parry2k
Super User
ferzfeld add new column using following expression:
Delta New = VAR __currentDate = Delta[entry_date] VAR __filter = ALLEXCEPT ( Delta, Delta[kpi_id],Delta[Notes] ) VAR __previousDate = CALCULATE ( MAX ( Delta[entry_date] ), __filter, Delta[entry_date] < __currentDate ) VAR __previousSales = CALCULATE ( MAX ( Delta[Actual] ), __filter, Delta[entry_date] = __previousDate ) RETURN IF ( NOT ISBLANK ( __previousSales ), Delta[Actual] - __previousSales )Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
ferzfeldWow this is amazing - providing exactly the result I have been looking for!
Thank you so much for your help, I really appreciate it 😊