Calculate date difference between two specific categories

[Tahech]

Hi guys,

Well, this problem is hard to explain, but here we go.

Consider this a tab with the following columns:

ID,

TITLE,

PHASE,

START_DATE, ( id enters the phase)

END_DATE, (id leaves the phase)

I want to calculate the difference between start and end date, this would be quite simple but...

I need to calculate the difference between the START_DATE of the phase "PROSPECT" and the START_DATE of "GAIN" phase.

And it needs to be calculated ONLY for the ID's that started in the "PROSPECT" phase until "GAIN" phase. 

Any other ID needs to be kept out of this measure.

Explaining what I want:

The ID's are clients, I want to calculate how long it takes for our clients who enters as a prospect to reach the gain phase, which means, become our client.

Thank-you in advance.

[amitchandak · Accepted solution]

@Tahech , You can take diff between various stage

example

new Column= 

Var _max = maxx(table, Table[ID] = earlier(Table[ID]) && [Start Date] < earlier([Start Date]) ), [Start Date])

return

datediff(_max, [Start Date], day)

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8