Forum Discussion

Gingerjeans88's avatar
Gingerjeans88
Icon for Helper IV rankHelper IV
6 years ago
Solved

Calculate time between stages across multiple rows with repeated unique ID

Hello,

I hope you’ll be able to help me.
I have a database of quotes, all with a unique ID, and every time the quote moves to a new stage a new row is added with the start and end date in that stage. The stage category is also named.

How do I count the time (in minutes) between each of the stages, per unique ID? Considering there are multiple entries for each ID?

I presume it needs a count and a max and min, then a diff in days, but I want the output in columns as it’s not the end visual, but needs to be used in an eventual visual.

Thanks!
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    Write this calculated column formula

    Duration = [Stage End Time]-[Stage Start Time]

    To your visual, drag Quote and write this measure

    =SUM(Data[Duration])

    Hope this helps.

5 Replies

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Gingerjeans88 ,

    Please read this post to get your question answered more quickly:

    https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

    If you could provide your pbix, or dummy values for both the present table, and expected results, that would be great.  Copy and paste the tables rather than a picture, so we can do the same into a pbix.
    Let me know if you have any questions.

    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
    Nathaniel

    • Gingerjeans88's avatar
      Gingerjeans88
      Icon for Helper IV rankHelper IV
      6 years ago

       I'm so sorry - first time posting! Thanks for the guidance. 

      QuoteStageStage Start Time  
      QUO100011. Initial Stage01/01/2019 12:3001/01/2019 12:34 
      QUO100221. Initial Stage10/02/2019 10:0511/02/2019 10:10 
      QUO100012. Secondary Stage01/01/2019 12:3501/01/2019 12:40 
      QUO100013. Tertiary Stage01/01/2019 12:4101/01/2019 12:50 
      QUO100222. Secondary Stage10/02/2019 10:1111/02/2019 10:13 
      QUO100221. Initial Stage10/02/2019 10:0511/02/2019 10:10 
      QUO100222. Secondary Stage10/02/2019 10:1111/02/2019 10:13 
      QUO100223. Tertiary Stage10/02/2019 10:1411/02/2019 10:20 

       

      I have chosen to use dummy data, is that format ok? I want to know the time between each of those stages for each record, and then I want to sum them later.

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        6 years ago

        Hi,

        Write this calculated column formula

        Duration = [Stage End Time]-[Stage Start Time]

        To your visual, drag Quote and write this measure

        =SUM(Data[Duration])

        Hope this helps.