Forum Discussion

nanma94's avatar
nanma94
Icon for Helper III rankHelper III
6 years ago
Solved

Opportunity 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 StageNa...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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.

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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's avatar
nanma94
Icon for Helper III rankHelper III
6 years ago

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.