Calculate Runtimes

[Anonymous]

Hi!

Newbie here, so hello @ all! I tried googling for the solution but can't find anything.

I have a table with data like this:

ID	Status	Date
1	Opened	01/01/20 14:40
1	Closed	01/02/20 07:49

Basically there are IDs, status changes and the date the status changed.

I already created measures that calculate the time from start to finish and the time between each status changes (Like the time from opened to scheduled, the time from scheduled to finish...). That works perfectly when I select a specific ID as a filter.

I now want to get the fastest and longest times for ID from start to finish overall and create a pie chart containing the percentage of every status of a selected ID. 

I figured that I have to create a calculated table for this, in which I can filter everything I want. What I don't understand how can I create a table that look like this:

ID

Time Opened to Closed

Time Opened to Scheduled

Time Scheduled to Closed

...

Can anyone help me?

Thank you very much!

[v-zhenbw-msft · Accepted solution]

Hi @Anonymous ,

We have created a sample, and please let us know if the construction does not meet yours.

1. We can create three measures to calculate the open time, close time, and schedule time.

open time = CALCULATE(MAX('Table'[Date]),'Table'[Status]="Opened")
close time = CALCULATE(MAX('Table'[Date]),'Table'[Status]="Closed")
schedule time = CALCULATE(MAX('Table'[Date]),'Table'[Status]="Scheduled")

2. Then we can calculate the difference between open time, close time and schedule time.

open to close = DATEDIFF([open time],[close time],MINUTE)
open to schedule = DATEDIFF([open time],[schedule time],MINUTE)
schedule to close = DATEDIFF([schedule time],[close time],MINUTE)

After put those measures to a pie chart, the result like this,

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

BTW, pbix as attached. 

Best regards,

Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you very much! This works!

Is there any way to apply this to all entries in the table? So I can get the worst time from start to finish and the best?

Again, thank you! You guys rock!

[Anonymous]

HI @ All, sorry to dig this out, but I have another little problem with this.

Using the pbix that @v-zhenbw-msft kindly provided as example. (I slightly altered the numbers here for better understanding).

I have two entries here, that have been calculated. There's a measure that's calculating the time from status "scheduled" to status "closed" 

I want to create an overview-page that shows which ticket took the longest time and which ticket was resolved the quickest.

Using a measure like

Quickest = min([open to close])

does not work here. Do I need to create a calculated table for this? Im confused.

In this example I want a Card that says "Quckest: 760" and one that reads "Longest: 770".

Again, thank you so much!

[Anonymous]

I figured it out! Just used the "MINX" and "MAXX" Functions. Thank you anyway 🙂

[MFelix]

Hi  @Anonymous ,

Are all the columns you are presenting measures that you calculated?

Have you tried to use the measure as values on your pie chart?