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

     

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 🙂 

  • Cortana's avatar
    Cortana
    Helper IV
    4 years ago

    I have added some sample data. 

    • 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

       

      • Cortana's avatar
        Cortana
        Helper IV
        4 years ago

        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.