Forum Discussion

Maximus's avatar
Maximus
Frequent Visitor
3 years ago
Solved

Calculation Overall Run time with overlapping time intervals

Hi,

 

Need a help for calculating sum of the job elapsed time, here the data between the jobs are overlapped(means few jobs started in parallel). 

Please help.

Here is the sample data.

 

From JOB_ID : 14 to 20 , the Start time and End Time are slightly overlapped ; and the JOB_ID 17 covers the overall period between(14 to 20)

 

Batch_DateJOB_IDPRCS_START_TIMEPRCS_END_TIME
2023-01-1312023-01-13 11:36:342023-01-13 11:37:18
2023-01-1322023-01-13 11:37:342023-01-13 11:39:34
2023-01-1332023-01-13 11:39:532023-01-13 11:44:20
2023-01-1342023-01-13 11:41:032023-01-13 11:41:34
2023-01-1352023-01-13 11:41:512023-01-13 11:43:30
2023-01-1362023-01-13 11:44:362023-01-13 11:45:41
2023-01-1372023-01-13 11:45:572023-01-13 11:47:25
2023-01-1382023-01-13 11:47:042023-01-13 11:47:40
2023-01-1392023-01-13 11:47:042023-01-13 11:47:40
2023-01-13102023-01-13 11:47:042023-01-13 11:47:41
2023-01-13112023-01-13 11:47:042023-01-13 11:47:41
2023-01-13122023-01-13 11:47:042023-01-13 11:47:41
2023-01-13132023-01-13 11:47:052023-01-13 11:47:42
2023-01-13142023-01-13 11:47:412023-01-13 11:49:01
2023-01-13152023-01-13 11:47:442023-01-13 11:48:20
2023-01-13162023-01-13 11:47:582023-01-13 11:49:30
2023-01-13172023-01-13 11:47:582023-01-13 11:50:12
2023-01-13182023-01-13 11:48:002023-01-13 11:49:52
2023-01-13192023-01-13 11:48:002023-01-13 11:49:36
2023-01-13202023-01-13 11:48:002023-01-13 11:49:36

 

 

Excepted output:

Total Elapsed Time for the below group : 2.14 minutes

2023-01-13 11:47:412023-01-13 11:49:01
2023-01-13 11:47:442023-01-13 11:48:20
2023-01-13 11:47:582023-01-13 11:49:30
2023-01-13 11:47:582023-01-13 11:50:12
2023-01-13 11:48:002023-01-13 11:49:52
2023-01-13 11:48:002023-01-13 11:49:36
2023-01-13 11:48:002023-01-13 11:49:36
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Maximus,

    You can try to use the following measure formula to get the max duration from current row context:

    formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [JOB_ID],
        [PRCS_START_TIME],
        [PRCS_END_TIME],
        "Diff", DATEDIFF ( 'Table'[PRCS_START_TIME], 'Table'[PRCS_END_TIME], SECOND )
    )
RETURN
    MAXX ( summary, [Diff] )

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Maximus,

    You can try to use the following measure formula to get the max duration from current row context:

    formula =
VAR summary =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        [JOB_ID],
        [PRCS_START_TIME],
        [PRCS_END_TIME],
        "Diff", DATEDIFF ( 'Table'[PRCS_START_TIME], 'Table'[PRCS_END_TIME], SECOND )
    )
RETURN
    MAXX ( summary, [Diff] )

    Regards,

    Xiaoxin Sheng