Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors