Calculating Sales Velocity Avg day duration per process and per stage

[icassiem]

Good day,

This is a tough one, and I am not following., Calculating Sales Velocity Avg day duration per process and per stage

I first, calculate the duration between each opportunity stage min and max date with:

HistoryDate_DurationDayActual = if(ABS(DATEDIFF('SalesForce_SalesStageVelocityActual'[HistoryCreatedDateMin],SalesForce_SalesStageVelocityActual[HistoryModifiedClosingDateMax].[Date],DAY))=0,1, ABS(DATEDIFF('SalesForce_SalesStageVelocityActual'[HistoryCreatedDateMin],SalesForce_SalesStageVelocityActual[HistoryModifiedClosingDateMax].[Date],DAY)))

now i am trying to average the duration based off the number of opportunities to give the avg opportunity process that includes 5 stages:

ActualDurationAVG =

VAR SelectedStartDate = CALCULATE(MIN(Calc_SpecialDates[Date]), filter(ALL(Calc_SpecialDates),Calc_SpecialDates[Period] = "FYTD"))

VAR SelectedEndDate = CALCULATE(MAX(Calc_SpecialDates[Date]), filter(ALL(Calc_SpecialDates),Calc_SpecialDates[Period] = "FYTD"))

//calc col

VAR StageDuration = calculate(sum(SalesForce_SalesStageVelocityActual[HistoryDate_DurationDayActual]),SalesForce_SalesStageVelocityActual[HistoryCreatedDateMin] >= SelectedStartDate,

SalesForce_SalesStageVelocityActual[HistoryCreatedDateMin] <= SelectedEndDate)

VAR MonthCount =

CALCULATE(

DISTINCTCOUNT(SalesForce_SalesStageVelocityActual[OpportunityID]),

SalesForce_SalesStageVelocityActual[HistoryCreatedDateMin] >= SelectedStartDate,

SalesForce_SalesStageVelocityActual[HistoryCreatedDateMin] <= SelectedEndDate)

RETURN

    DIVIDE(StageDuration, MonthCount)

i am not feeling comfortable this is accurate and i need a second calcated measure to give me the AVG  Duration per Stage accross all opportunites

Please help, i am lost

Regards

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[icassiem]

@lbendlin  Thank you for assisting

Attached is my sample dataset anonymised 

i am trying to get my average day which is the calc column that gives me the day dureation and then average by opportunity, and then i want to avg per stage. the structure is the an opportunity contains multiple stages

Please help me

OpportunityID	OpportunityName	OpportunityHistoriesStageName	HistoryCreatedDateMin	HistoryModifiedClosingDateMax
0064**********GQAY	Gen****************************************************port (WS1)	Closed Gone Away	2025/05/29	2025/05/29
0064**********GQAY	Gen****************************************************port (WS1)	Stalled	2023/08/05	2025/05/29
0064**********KQAW	Amc******************************************** (ACC LWA)	Closed Gone Away	2025/05/16	2025/05/16
0064**********KQAW	Amc******************************************** (ACC LWA)	Stalled	2023/11/29	2025/05/16
0064**********GQAQ	Sup************************pportunity	NDA not Executed	2023/11/07	2023/11/07
0064**********ZQAZ	#VALUE!	Stalled	2024/01/23	2024/01/23
0064**********cQAD	AB ******************id Results	Closed Gone Away	2025/01/22	2025/01/22
0064**********UQA1	Ime****erys (WS1)	Stalled	2023/11/14	2023/12/07
0064**********qQAJ	SNF********S/C TRACCS	Closed Gone Away	2024/01/12	2024/01/12
0064**********qQAJ	SNF********S/C TRACCS	Stalled	2023/12/07	2024/01/12
0064**********BQAR	on-***************************F interest	Closed Gone Away	2023/11/07	2023/11/07
0064**********RQA3	Fin**********************ills (WS1)	Won/Closed	2023/08/10	2023/08/10
0064**********RQA3	Fin**********************ills (WS1)	Legal Negotiation	2023/07/20	2023/08/10
0064**********RQA3	Fin**********************ills (WS1)	Contract Negotiations	2023/06/26	2023/07/20
0064**********6QAP	Yie************************EMENTATION	Closed Gone Away	2025/01/22	2025/01/22
0064**********hQAJ	Dou********************************************ing - MAKE	Stalled	2024/06/23	2025/07/11
0064**********hQAJ	Dou********************************************ing - MAKE	Proposal Evaluation	2023/08/05	2024/06/03
0064**********hQAJ	Dou********************************************ing - MAKE	Contract Negotiations	2023/08/07	2023/08/24
0064**********hQAJ	Dou********************************************ing - MAKE	Design	2023/06/26	2023/08/05
0064**********nQAJ	Gra************************************TRACC Make	Closed Gone Away	2024/10/03	2024/10/03
0064**********nQAJ	Gra************************************TRACC Make	Stalled	2024/06/18	2024/10/03
0064**********qQAF	Dou*********Good-  NDA	Executed NDA	2023/06/21	2023/06/21

[icassiem]

@lbendlin 

Hi, any help, please?

[lbendlin · Accepted solution]

There are many ways to interpret that.  And even opportunities marked as "Closed, gone away"  are not always really dead - they can be revived and go back to a prior state.

You can only calculate this if you have more than one event per opportunity, or if you assume that the last stage extends until "Today".

Technically "c" should be "c-1" - but that's up for debate too.

[icassiem]

@lbendlin hi, i am lost

Would this give me avg day duratuon per stage, then avg day for  opportunity cycle, then the avg stage day dur?

An opportunity has 5 to 6 stages witjin a cycle, but nit all opportubities ends at variois stages witjin a opp cycle.

1. So the avg  no of day per stage i want to put in a horizontal line bar

2. The avg opportunity no of days, meaning on avg how long sn ppportubity takes

3..The avg stage no of days, meaning on avg how long a stage takes

Could it be done?

[lbendlin]

Do you only want to include "past"  stages (ie stages that have been superseded) or do you also want to include "current"  stages without an end date?

[icassiem]

@lbendlin 

My scorecard is filtered on all opportunities stages started/initiated within a FY year (Jun to May), with the HistoryCreatedDateMin being the key date to calendar and filter range

so if the stage or opportuntity still open, use the current date to ensure it is included in calculations
i would like these 3 measures

1. So the avg  no of day per stage i want to put in a horizontal line bar

2. The avg opportunity no of days, meaning on avg how long sn ppportubity takes

3..The avg stage no of days, meaning on avg how long a stage takes

[lbendlin]

what's the difference between1 and 3?

[icassiem]

@lbendlin  , Hi, the difference, 1 is the avg per stage, 3 is the avg day dur a stage takes within an opportunity takes

4. But now I need to calculate the variance between yoy, which is 1 and 2 vs yoy, does this mean I replicate what we do for 1 and 2 actuals then calculate the variance to history ?

[icassiem]

@lbendlin thank you, all good

I had to make copies of the dataset in various granularities