Forum Discussion
Helper IIIOpportunity stage duration
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
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.
9 Replies
CortanaHelper IV
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?
- parry2k
Super User
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.
nanma94I've had a calculated column that works to average by stage. But this measure provides more flexibility. Thank you so much!
- 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.
- Ashish_Mathur
Hi,
Please show the exact result that you are expecting in a simple table.
