Calculate time between stages across multiple rows with repeated unique ID

[Gingerjeans88]

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!

[Nathaniel_C]

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]

Hi @Nathaniel_C , Response above!

[Gingerjeans88]

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

Quote	Stage	Stage Start Time
QUO10001	1. Initial Stage	01/01/2019 12:30	01/01/2019 12:34
QUO10022	1. Initial Stage	10/02/2019 10:05	11/02/2019 10:10
QUO10001	2. Secondary Stage	01/01/2019 12:35	01/01/2019 12:40
QUO10001	3. Tertiary Stage	01/01/2019 12:41	01/01/2019 12:50
QUO10022	2. Secondary Stage	10/02/2019 10:11	11/02/2019 10:13
QUO10022	1. Initial Stage	10/02/2019 10:05	11/02/2019 10:10
QUO10022	2. Secondary Stage	10/02/2019 10:11	11/02/2019 10:13
QUO10022	3. Tertiary Stage	10/02/2019 10:14	11/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 · Accepted solution]

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.

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.