Calculated Column for delta calculation with previous row (lead/lag)

[ferzfeld]

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

[parry2k · Accepted 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.⚡

[ferzfeld]

Wow this is amazing - providing exactly the result I have been looking for!

Thank you so much for your help, I really appreciate it 😊