Opportunity stage duration

[nanma94]

I have an opportunity header table, that stores the latest stage, and I will ultimately want to analyze opportunity cycle time (duration per stage) by owner, territory. 

OpportunityId	StageName	Owner	Territory
1	4 .Closed Won	10	T1
2	3 .Negotiation	11	T2
3	2 .Diligence	12	T1

 I also have a opportunity history table, that has the timestamp for each stage:

OpportunityId	CreatedDate	Stage Name	Stage #
1	2/26/2020	1 .Targeting	1
1	2/27/2020	2 .Diligence	2
1	2/28/2020	3 .Negotiation	3
1	2/29/2020	3 .Negotiation	3
1	3/20/2020	4 .Closed Won	4
1	3/23/2020	4 .Closed Won	4
1	3/31/2020	5 .Grow / Sustain	5
2	4/3/2020	1 .Targeting	1
2	4/5/2020	2 .Diligence	2
2	4/10/2020	3 .Negotiation	3
3	1/5/2020	1 .Targeting	1
3	1/15/2020	2 .Diligence	2

How do I go about calculating the average stage duration, which later I want the flexilble to slice by owner, territory?

Thank you so much!
NM

[parry2k]

@Cortana I'm also confused what you are referring too, if you can provide more details how you get to 11 days, it would help.

[Cortana]

Look at the created date of stage 4. The opportunity entered stage 4 on 20th March and It entered stage 5 on 31st March. So this opportunity stays 11 days in stage 4, right? 

[Ashish_Mathur]

Hi,

Please show the exact result that you are expecting in a simple table.

[parry2k]

@nanma94 which date to pick when oppy has same stage twice, like in your example, oppy #1 has two closed won stages on different dates

[nanma94]

@parry2k  Thank you for looking into this. Yes, It is a data quality issue (I wanted to highlight but forgot to mention) , which I am thinking I will pick the min of earlier stage and max of later stage to get the duration. 

I am going to come back report after I try your solution out. Thank you so much in advance. 

[parry2k · Accepted solution]

@nanma94 try measure below and here is the result.

Days between Stages = 
VAR __oppyId = SELECTEDVALUE ( Oppy[OpportunityId] )
VAR __Stage = MAX ( 'Oppy Detail'[Stage #] )
VAR __StageDate = MAX ( 'Oppy Detail'[CreatedDate] )
VAR __prevStage = MAX ( __Stage - 1, 1 )
VAR __prevStageDate = 
CALCULATE ( 
    MAX ( 'Oppy Detail'[CreatedDate] ),
    ALL ( 'Oppy Detail' ), 
    Oppy[OpportunityId] = __OppyId, 
    'Oppy Detail'[Stage #] = __prevStage 
)
RETURN DATEDIFF( __prevStageDate, __StageDate, DAY )

Would appreciate Kudos 🙂 if my solution helped.

[Cortana]

Hello @parry2k . 

If I get it correctly, you are calculating stage duration, right? I mean the total time it takes in each stage. 

If so, then for the first opportunity the total days for staying in stage 4 would be 11 days. But it's not showing that. I am confused. Can you help to understand that?

[nanma94]

I've had a calculated column that works to average by stage. But this measure provides more flexibility. Thank you so much!

[parry2k]

@nanma94 Glad I could help.