Forum Discussion

Cortana's avatar
Cortana
Helper IV
4 years ago
Solved

Calculating Stage Duration

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

Now I want the following output.

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 

Need Help
Tips and Tricks
Tutorial Requests
  • NickolajJessen's avatar
    NickolajJessen
    4 years ago

    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's avatar
    NickolajJessen
    4 years ago

    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

     

8 Replies

  • NickolajJessen's avatar
    NickolajJessen
    Solution Sage
    4 years ago

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

      • NickolajJessen's avatar
        NickolajJessen
        Solution Sage
        4 years ago

        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