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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Cortana
Helper III
Helper III

Calculating Stage Duration

I want to calculate how long the opportunities are staying in each stage. I have this.

WhatIhave.PNG

Now I want the following output.

WhatIwant.PNG

Here my condition is- for example - the duration of stage 3 is (1/25/2022 - 12/28/2021) = 28 days. 

But the exception is only the last one, which is the difference between today's date (6/2/2022). 

Is there any way to do the whole thing in PBI? 

Here is some sample data.

SampleData 

2 ACCEPTED SOLUTIONS

Awesome! Give this a shot
Create Measure 

Rankx = 
RANKX(
    FILTER(ALL(Tabel), Tabel[OpportunityID] = MAX(Tabel[OpportunityID])),
    CALCULATE(
        max(Tabel[CreatedDate])
    )
    ,,ASC
)

And then this second measure

NickolajJessen_0-1654237582532.png

 

View solution in original post

My mistake. i read it as average stage.
(0+58+52+0+1+18+2+10+22+108+58+71) / Number og stages (12) = 31.5

Try this in stead

NickolajJessen_0-1654256108875.png

 

View solution in original post

8 REPLIES 8
NickolajJessen
Solution Sage
Solution Sage

If you can share some data in a table format for easy entry into power bi, that would be nice 🙂 

I have added some sample data. 

Awesome! Give this a shot
Create Measure 

Rankx = 
RANKX(
    FILTER(ALL(Tabel), Tabel[OpportunityID] = MAX(Tabel[OpportunityID])),
    CALCULATE(
        max(Tabel[CreatedDate])
    )
    ,,ASC
)

And then this second measure

NickolajJessen_0-1654237582532.png

 

Thank you so much. Saves the day. @NickolajJessen 

But I am stuck in another approach. Basically, I need to calculate the average duration of each stage. That is (the count of days/Number of opportunities) in each stage. 

I can not use the stageDays in the new calculated measure as it's a secondary variable.

NickolajJessen_0-1654250757364.png

Note that this also counts the last stage comparing the starting dato to TODAY.

 

@NickolajJessen 

I think there is a mistake. If I do that It gives an output like this picture (filtered on Stage 1 Closed Won).

Prob.PNG

Here, there are 5 opportunities in Closed Won, and the total days for all the 5 opportunities in stage Closed Won is 400.  So the average would be 400/5 = 80. 

I was thinking about this.

My mistake. i read it as average stage.
(0+58+52+0+1+18+2+10+22+108+58+71) / Number og stages (12) = 31.5

Try this in stead

NickolajJessen_0-1654256108875.png

 

Thank you so much @NickolajJessen. It worked 😀.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.