Pairing based on calendar and category

[sonet141]

Hi guys,

In need desperatly your help on this one.

Few details on my fields:

ID: Basic identification

Status: from 0(creation of the ID) to 100 (final status). A status can move forward and backward. The date will always increase.

Date updated: First time the rows/status has been changed.

Table that I work from:

ID	Status	Date updated
1	10	1/1/2021
1	50	1/4/2021
1	80	1/7/2021
2	20	1/3/2021
2	70	2/10/2021
2	30	2/12/2021
3	50	12/12/2021
3	70	12/30/2021
3	60	1/1/2021

End Table that I wish to have:

Date of Change	Status Change	ID Impacted
12/12/2020	0->50	2
12/30/2020	50->70	3
1/1/2021	0->10	1
1/1/2021	70->60	3
1/3/2021	0->20	2
1/4/2021	10->50	1
1/7/2021	50->80	1
2/10/2021	20->70	2
2/12/2021	70->30	2

I need to figure out my DAX query to get this result. Thanks 🙂

[parry2k · Accepted solution]

@sonet141 here is the DAX expression to add new column:

Status Change = 
VAR __table = ALLEXCEPT ( 'Status', 'Status'[ID] ) 
VAR __currentDate = 'Status'[Date updated]
VAR __prevStatus = COALESCE ( CALCULATE ( MAX ( 'Status'[Status] ), __table, 'Status'[Date updated] < __currentDate ), 0 )
VAR __result = FORMAT ( __prevStatus, "General Number" ) & " -> " & FORMAT ( 'Status'[Status], "General Number" )
RETURN
__result

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.⚡

[sonet141]

Working like a charm !

Thanks lad 🙂