Idle time calculation to show in a d:h:m: format and be sumable

[Karolina_95]

Hi, I need to calculate the idle time between two colums (Next Arrival and previous departure). The calculation is working so that's great but the difference is shown in minutes as when counted in hours it's rounding up and not showing the precise difference.

I want to have that difference displayed in a day:hour:minute format and also be able to sum it up so I can use it for calculations.

Unfortunately I can't use the measure as the idle time calculation doesn't show there nor I can't calculate the idle time as a measure.

Arrival Time               Depart Time

29/12/2022 12.52	29/12/2022 21.40
02/01/2023 08.10	02/01/2023 13.20
03/01/2023 13.30	03/01/2023 23.10
04/01/2023 00.15	04/01/2023 10.10
04/01/2023 12.07	04/01/2023 20.00
04/01/2023 21.45	05/01/2023 02.00
05/01/2023 02.40	05/01/2023 09.10
05/01/2023 11.27	05/01/2023 17.50
05/01/2023 19.45	06/01/2023 01.00
06/01/2023 03.00	06/01/2023 09.00
06/01/2023 10.55	06/01/2023 15.59
06/01/2023 18.55	07/01/2023 03.00
07/01/2023 08.00	07/01/2023 17.15

The idle time calculation is 

Idle time2 = VAR prevEnd =

CALCULATE (

MAX ( Table13[Depart Time]),

FILTER (

ALL ( Table13 ), [Arrival Time] < EARLIER ( Table13[Arrival Time] )

)

)

RETURN

DATEDIFF (Table13[Arrival Time], prevEnd, MINUTE) *-1

Is there any way to have it working?

[Karolina_95]

Hi @Anonymous thanks for the solution!

I think that should work however for some reason when adding it into the report I got an error message with Find function. Do you have any ideas why that is happening? The code doesn't show any errors after I copied and adjusted it to my data.

[Anonymous · Accepted solution]

Hi @Karolina_95 ,

Please try below steps:

1. below is my test table
Table:

2. create a measure with below dax formula

Day Hour Minute =
VAR cur_dt =
    SELECTEDVALUE ( 'Table'[Depart Time] )
VAR cur_at =
    SELECTEDVALUE ( 'Table'[Arrival Time] )
VAR pre_dt =
    CALCULATE (
        MAX ( [Depart Time] ),
        FILTER ( ALL ( 'Table' ), [Arrival Time] < cur_at )
    )
VAR _val =
    DATEDIFF ( pre_dt, cur_at, MINUTE )
VAR _val1 =
    FORMAT ( _val / 1440, "0.00" )
VAR _val2 =
    IF ( ISBLANK ( _val1 ), BLANK (), FIND ( ".", _val1 ) - 1 )
VAR _val3 =
    FORMAT ( MOD ( _val, 1440 ) / 60, "0.00" )
VAR _val4 =
    IF ( ISBLANK ( _val3 ), BLANK (), FIND ( ".", FORMAT ( _val3, "0.00" ) ) - 1 )
VAR _a =
    IF ( ISBLANK ( _val ), BLANK (), MID ( _val1, 1, _val2 ) )
VAR _b =
    IF ( ISBLANK ( _val ), BLANK (), MID ( _val3, 1, _val4 ) )
VAR _c =
    IF ( ISBLANK ( _val ), BLANK (), MOD ( MOD ( _val, 1440 ), 60 ) )
RETURN
    IF ( ISBLANK ( _val ), BLANK (), _a & ":" & _b & ":" & _c )

3. add  a table visual with table fields and measure

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.