Hello,
I have an opportunity table which is linked to Opportunity_log table by the ID. Opportunity_log has the information about all the stages of the opportunity along with the status (Status W = Won) and Dates.
I need your help with calculating the following:
Average time spent on each stage of an opportunity till it is won.
Opportunity_log only provides the date when the opportunity was entered into a particular Stage but does not provide the end date. So, In order to calculate the time spent, need to calculate the difference between date when the opportunity entered into the stage (which is given in opportunity_log table) and the date of the next stage of the same opportunity.
I have attached the pbix file below.
CRM-Test.pbix (376.9 KB)
Thank you very much in advance!
Hi @gkhokher
Please receive a workout of your request.
If you have further questions or remarks, please let me know.
PBIX attached : CRM-Test v3.pbix (619.2 KB)
Steps taken:
- Add a calculated column in Opportunity Log table, which calculates the time in minutes between the (maximal) 6 stages
Time betw Stages = // calculates difference in minutes between consecutive stages of an OppID
var OpportunityID = OppLogT[OppID] // 364 and higher
var Stage = OppLogT[StageID] // 1-6
Var PreviousStage = IF(stage = 1, stage, Stage - 1) //if stage 1, then Time Diff Stage 1 per definition 0 minutes
var PrevStage =
CALCULATE( MAX(OppLogT[Modified]), //date time of a stage
FILTER(OppLogT, OppLogT[OppID] = OpportunityID && OppLogT[StageID] = PreviousStage ))
return
DATEDIFF(PrevStage, OppLogT[Modified], MINUTE)
-
Note: stage 6 has always two lines (see example below), including one with the Status “W(in)”, sometimes with big time differences.
-
Note: much time appears between stages, when “Minute” is used in DateDiff, too big figures ? When using “Day” instead, most of the time per stage will be zero.
-
Make five similar measures with average times of the stages from winning opportunities. 89 wins in the data
Av.Time Stage 1 to 2 = CALCULATE( AVERAGE( OppLogT[Time betw Stages]),
FILTER( OppLogT, OppLogT[StageID] = 2),
FILTER( OpportunityT, OpportunityT[Status]= "W"))
- Note: when determining the time between Stage 5 and 6, the timestamp at Status “W” is taken from Stage 6 from the Opportunity-Log table , see therefore measure “Av.Time Stage 5 to 6”
-
An example report is made of the Winning Opportunities (big numbers when using minutes)
Hope this answers your request,
kind regards, JW
4 Likes
