Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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_Date | JOB_ID | PRCS_START_TIME | PRCS_END_TIME |
2023-01-13 | 1 | 2023-01-13 11:36:34 | 2023-01-13 11:37:18 |
2023-01-13 | 2 | 2023-01-13 11:37:34 | 2023-01-13 11:39:34 |
2023-01-13 | 3 | 2023-01-13 11:39:53 | 2023-01-13 11:44:20 |
2023-01-13 | 4 | 2023-01-13 11:41:03 | 2023-01-13 11:41:34 |
2023-01-13 | 5 | 2023-01-13 11:41:51 | 2023-01-13 11:43:30 |
2023-01-13 | 6 | 2023-01-13 11:44:36 | 2023-01-13 11:45:41 |
2023-01-13 | 7 | 2023-01-13 11:45:57 | 2023-01-13 11:47:25 |
2023-01-13 | 8 | 2023-01-13 11:47:04 | 2023-01-13 11:47:40 |
2023-01-13 | 9 | 2023-01-13 11:47:04 | 2023-01-13 11:47:40 |
2023-01-13 | 10 | 2023-01-13 11:47:04 | 2023-01-13 11:47:41 |
2023-01-13 | 11 | 2023-01-13 11:47:04 | 2023-01-13 11:47:41 |
2023-01-13 | 12 | 2023-01-13 11:47:04 | 2023-01-13 11:47:41 |
2023-01-13 | 13 | 2023-01-13 11:47:05 | 2023-01-13 11:47:42 |
2023-01-13 | 14 | 2023-01-13 11:47:41 | 2023-01-13 11:49:01 |
2023-01-13 | 15 | 2023-01-13 11:47:44 | 2023-01-13 11:48:20 |
2023-01-13 | 16 | 2023-01-13 11:47:58 | 2023-01-13 11:49:30 |
2023-01-13 | 17 | 2023-01-13 11:47:58 | 2023-01-13 11:50:12 |
2023-01-13 | 18 | 2023-01-13 11:48:00 | 2023-01-13 11:49:52 |
2023-01-13 | 19 | 2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
2023-01-13 | 20 | 2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
Excepted output:
Total Elapsed Time for the below group : 2.14 minutes
2023-01-13 11:47:41 | 2023-01-13 11:49:01 |
2023-01-13 11:47:44 | 2023-01-13 11:48:20 |
2023-01-13 11:47:58 | 2023-01-13 11:49:30 |
2023-01-13 11:47:58 | 2023-01-13 11:50:12 |
2023-01-13 11:48:00 | 2023-01-13 11:49:52 |
2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
2023-01-13 11:48:00 | 2023-01-13 11:49:36 |
Hi @Maximus ,
You can create a calculated column as below to get it, please find the details in the attachment.
Column =
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table'[PRCS_END_TIME] ),
FILTER (
'Table',
'Table'[Batch_Date] = EARLIER ( 'Table'[Batch_Date] )
&& 'Table'[PRCS_START_TIME] = EARLIER ( 'Table'[PRCS_START_TIME] )
)
)
VAR _nendtime =
CALCULATE (
MAX ( 'Table'[PRCS_END_TIME] ),
FILTER (
'Table',
'Table'[Batch_Date] = EARLIER ( 'Table'[Batch_Date] )
&& 'Table'[PRCS_START_TIME] = EARLIER ( 'Table'[PRCS_START_TIME] )
)
)
VAR _Seconds =
DATEDIFF ( 'Table'[PRCS_START_TIME], _nendtime, SECOND )
VAR _Minutes =
INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds =
MOD ( _Seconds, 60 )
VAR _Hours =
INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes =
MOD ( _Minutes, 60 )
RETURN
IF (
_count > 1
&& 'Table'[PRCS_END_TIME] = _nendtime,
FORMAT ( _Hours, "00" ) & ":"
& FORMAT ( _RemainingMinutes, "00" ) & ":"
& FORMAT ( _RemainingSeconds, "00" ),
BLANK ()
)
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
35 | |
32 | |
18 | |
18 |