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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.