To calculate dates difference in the same column, where criteria fits

[JohnnyK]

Good day!

Data as below.

I want to calculate, for each Project, the difference in days from a "Case Open" (false to true) to File Status" Rejected.

In the example, Project KCG’s calculation is (using the Edit Date column): 29/07/2021 - 28/07/2021 = 1 day

In PowerBI, what is the good way to do so? Would there be a way to restructure the data, a formula’s better?

(note: the data has hundreds of rows, that not each Project has the row of "Case Open" (false to true) and File Status" Rejected.)

Thank you.

Project	Origin	Staff	Created Date	Edit Date	Milestone	Value before	Value after
GIN	David	Mike	04/09/2021	04/12/2021	Case Open	false	true
GIN	David	Mike	04/09/2021	04/12/2021	Completed
KCG	Peter	Jordon	07/06/2021	28/07/2021	Case Open	false	true
KCG	Peter	Jordon	07/06/2021	29/07/2021	File Status	New	Rejected
PQE	Lilly	MJ	07/08/2021	19/07/2021	File Status	New	Accepted

[JohnnyK]

@amitchandak, fantastic! thank you for sharing of knowledge!

[amitchandak · Accepted solution]

@JohnnyK , Create a new column like

new column =
var _1 = maxx(filter(Table, [Edit Date] <earlier([Edit Date]) && [Project] =earlier([Project]) && [Milestone] ="Case Open"),[Edit Date])
return
if(not(isblank(_1)) && [Value after] ="Rejected", datediff(_1, [Edit Date],day), blank())