Calculate date difference between two specific categories

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.

I did the same question in PBI Community: https://community.powerbi.com/t5/Desktop/Calculate-date-difference-between-two-specific-categories/m-p/3068863#M1041877

Solution

You need something like this, where you calculate the start dates for the two phases of interest, but retain a filter on the selected ID:

Diff = 
VAR _prospect = 
    CALCULATE (
        MAX ( 'Table'[START_DATE] ) , 
        ALLEXCEPT ( 
            'Table' ,
            'Table'[ID]
        ) , 
        'Table'[PHASE] = "PROSPECT"
    )
VAR _gain = 
    CALCULATE (
        MAX ( 'Table'[START_DATE] ) , 
        ALLEXCEPT ( 
            'Table' ,
            'Table'[ID]
        ) , 
        'Table'[PHASE] = "GAIN"
    )
RETURN 
IF ( 
    HASONEVALUE ( 'Table'[ID] )  
      && _prospect <> BLANK () 
      && _gain <> BLANK () ,    
    DATEDIFF ( _prospect , _gain , DAY )
)

Here I also remove any cases where either _prospect or _gain are evaluated to be blank for the given ID. I also short-circuit the calculation if there are more than 1 IDs present.

If you want the average for your IDs then you can look at a measure like this, using the same measure in an iterator:

Avg Diff = 
AVERAGEX ( 
    VALUES ( 'Table'[ID] ) , 
    [Diff]
)