cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
nanma94
Helper III
Helper III

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. 

 

OpportunityIdStageNameOwnerTerritory
14 .Closed Won10T1
23 .Negotiation11T2
32 .Diligence12T1

 

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

OpportunityIdCreatedDateStage NameStage #
12/26/20201 .Targeting1
12/27/20202 .Diligence2
12/28/20203 .Negotiation3
12/29/20203 .Negotiation3
13/20/20204 .Closed Won4
13/23/20204 .Closed Won4
13/31/20205 .Grow / Sustain5
24/3/20201 .Targeting1
24/5/20202 .Diligence2
24/10/20203 .Negotiation3
31/5/20201 .Targeting1
31/15/20202 .Diligence2

 

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

1 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.

image.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

9 REPLIES 9
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
parry2k
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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. 

@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.

image.png

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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?

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

@nanma94 Glad I could help. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors