Calculate elapsed time in aggregation

[AndyKing]

Hi,

I have a timeseries data imported to PowerBI Desktop. The data is in table format and it is composed of processes, sub process, sub process StartTime and subprocess EndTime. Each process has multiple sub processes. Each sub process is scheduled to run everyday. Sub processes run concurrently and independently. For a process to be completed, all of its sub processes should be completed.

Below is a sample data for illustration purposes. I need to calculate the elapsed time for each distinct process for each day. To calculate the elapsed time for a process, we need to find the sub process with the earliest time and we also need to find the sub process with the latest finish time and then we need to take the time difference between the finish time and start time (in minutes) to calculate the elapsed time for each process.
Table1: Processes and SubProcesses
Process SubProcess     StartTime           EndTime            ElapsedTime (min)
P1         P1_Sub1         1/01/2022 9:00  1/01/2022 9:10  10
P1         P1_Sub2         1/01/2022 9:02  1/01/2022 9:15  13
P1         P1_Sub3         1/01/2022 9:10  1/01/2022 9:30  20
P2         P2_Sub1         1/01/2022 9:15   1/01/2022 9:20  5
P2         P2_Sub2         1/01/2022 9:20  1/01/2022 9:30  10
P2         P2_Sub3         1/01/2022 9:20  1/01/2022 9:40  20
P2         P2_Sub4         1/01/2022 9:30  1/01/2022 9:45  15
P1         P1_Sub1         2/01/2022 9:00  2/01/2022 9:12  12
P1         P1_Sub2         2/01/2022 9:05 2/01/2022 9:14    9
P1         P1_Sub3         2/01/2022 9:10 2/01/2022 9:35    25
P2         P2_Sub1         2/01/2022 9:15 2/01/2022 9:20     5
P2         P2_Sub2         2/01/2022 9:20 2/01/2022 9:35    15
P2         P2_Sub3         2/01/2022 9:20 2/01/2022 9:35    15
P2         P2_Sub4         2/01/2022 9:30 2/01/2022 9:43    13

For example, in Table1, process P1 has 3 sub processes and among all 3 sub processes, the one that starts the earliest is P1_Sub (which has a start time of "1/01/2022 9:00") and the sub process that finishes the latest is P1_Sub3 (which has a finish time of "1/01/2022 9:30"). So the elapsed time for the process P1 on the day 1/01/2022 was 9:30-9:00=30 minutes.
I need a measure to calculate "the elapsed time for each process for each day" similar to Table2 below.
Table2: Process Elapsed Time
Process StartTime            EndTime              ElapsedTime (min)
P1         1/01/2022 9:00    1/01/2022 9:30   30
P2         1/01/2022 9:15    1/01/2022 9:45   30
P1         2/01/2022 9:00    2/01/2022 9:35   35
P2         2/01/2022 9:15    2/01/2022 9:43   28

Can you please help me with the DAX formula?
Thanks
Andy

[Anonymous · Accepted solution]

Hi  @AndyKing ,

Here are the steps you can follow：

1. In Power query. Add Column – Index Column – From 1.

2. Create calculated column.

IF =
var _1=
VALUE(RIGHT('Table'[SubProcess],1))
return
IF(_1=1,1,0)

Group =
SUMX(FILTER(ALL('Table'),'Table'[Index]<=EARLIER('Table'[Index])),[IF])

3. Create measure.

StartTime_Measure =
MINX(FILTER(ALL('Table'),
'Table'[Process]=MAX('Table'[Process])&&YEAR('Table'[StartTime])=YEAR(MAX('Table'[StartTime]))
&&MONTH('Table'[StartTime])=MONTH(MAX('Table'[StartTime]))&&DAY('Table'[StartTime])=DAY(MAX('Table'[StartTime]))),[StartTime])

EndTime_Measure =
MAXX(FILTER(ALL('Table'),
'Table'[Process]=MAX('Table'[Process])&&YEAR('Table'[EndTIme])=YEAR(MAX('Table'[EndTIme]))
&&MONTH('Table'[EndTIme])=MONTH(MAX('Table'[EndTIme]))&&DAY('Table'[EndTIme])=DAY(MAX('Table'[EndTIme]))),[EndTIme])

ElapsedTime (min) =
DATEDIFF(
    [StartTime_Measure],[EndTime_Measure],MINUTE)

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[AndyKing]

Hi @Greg_Deckler,
Thank you very much for the prompt response.
Shouldn't there be a loop to calculate the elapsed time for each process?
In the example above, MIN (StartTime) and MAX(EndTime) should be calculated within each process for each day, so we need to get 4 different elapsed times, two for each day because there are two processes.   
Hope that helps.

[Greg_Deckler]

@AndyKing Well, you would need something to differentiate the rows in your table visual. You could add a Day column to your table as a calculated column for example. Something like: DATE(YEAR([StartTime],MONTH([StartTime],DAY([StartTime])) and add that to your visual but don't summarize.

[Greg_Deckler]

@AndyKing Try:

ElapsedTime (min) Measure =
  VAR __Start = MIN('Table'[StartTime])
  VAR __End = MAX('Table'[EndTime])
  VAR __Result = DATEDIFF(__Start, __End, MINUTE)
RETURN
  __Result