cancel
Showing results 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

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.

 OpportunityId StageName Owner Territory 1 4 .Closed Won 10 T1 2 3 .Negotiation 11 T2 3 2 .Diligence 12 T1

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

 OpportunityId CreatedDate Stage Name Stage # 1 2/26/2020 1 .Targeting 1 1 2/27/2020 2 .Diligence 2 1 2/28/2020 3 .Negotiation 3 1 2/29/2020 3 .Negotiation 3 1 3/20/2020 4 .Closed Won 4 1 3/23/2020 4 .Closed Won 4 1 3/31/2020 5 .Grow / Sustain 5 2 4/3/2020 1 .Targeting 1 2 4/5/2020 2 .Diligence 2 2 4/10/2020 3 .Negotiation 3 3 1/5/2020 1 .Targeting 1 3 1/15/2020 2 .Diligence 2

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

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

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.

9 REPLIES 9
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.

Helper III

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?

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
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.

Helper III

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

Super User

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

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.

Helper III

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?

Helper III

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

Super User

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.

Announcements

#### 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 Monthly Update - June 2024

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

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors