Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Wow this is amazing - providing exactly the result I have been looking for!
Thank you so much for your help, I really appreciate it 😊
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |