Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ferzfeld
Advocate II
Advocate II

Calculated 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_idNotesActualentry_dateDelta
AAASamson10031.01.2019 00:00 
AAASamson12028.02.2019 00:0020
AAAPeter4031.03.2019 00:00 
AAAPeter4530.04.2019 00:005
AAAPeter4831.05.2019 00:003
AAAPeter5030.06.2019 00:002
AAAPeter5531.08.2019 00:005
AAASamson15030.09.2019 00:0030
AAASamson20031.10.2019 00:0050
AAASamson25031.01.2020 00:0050
AAASamson29129.02.2020 00:0041
BBBElmo529.02.2020 12:00 
BBBElmo731.03.2020 12:002
AAASamson29530.04.2020 10:224
BBBElmo1030.04.2020 12:003
BBBElmo1631.05.2020 12:006
AAASamson35030.06.2020 09:4255
BBBElmo2030.06.2020 12:004
BBBElmo2531.07.2020 12:005
AAAPeter6031.07.2020 12:125
AAAPeter5831.08.2020 10:17-2
BBBElmo2831.08.2020 16:083
AAASamson32030.09.2020 14:22-30
BBBElmo3530.09.2020 16:077
AAAPeter7031.10.2020 07:5312
BBBElmo4031.10.2020 17:065
BBBElmo4530.11.2020 14:455
BBBElmo3831.12.2020 09:39-7
AAASamson35031.12.2020 10:3930
BBBElmo2801.01.2021 10:59-4
AAAPeter9031.01.2021 16:1120



1 ACCEPTED SOLUTION
parry2k
Super User
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.



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.

View solution in original post

2 REPLIES 2
parry2k
Super User
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.



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 😊

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.