Hours and minutes between last shift of day and first shift of next day

[Qotsa]

Hi,

How do you calculate for each carer_id, the difference in hours and minutes between their last shift & the start of their next shift on the following day?

Below, carer_id 78315, their last shift on 18/11/2021 is 20:40

&

their first shift the next day 19//11/2021 is 08:00

so result should be 11hrs 20 mins.

[v-zhangti]

Hi, @Qotsa 

Your problem can be solved by adding several calculation columns. You can try the following methods.

MIN =
CALCULATE (
    MIN ( 'Table'[Sched_Start] ),
    FILTER ( 'Table', [date] = EARLIER ( 'Table'[date] ) )
)

date2 = [date]-1

MAX =
CALCULATE (
    MAX ( 'Table'[Sched_Finsh] ),
    FILTER ( 'Table', [date] = EARLIER ( 'Table'[date2] ) )
)

Duration =
IF ( [MAX] = BLANK (), BLANK (), ( [date] + [MIN] ) - ( [date2] + [MAX] ) )

The calculation column is the decomposition step, which is convenient for you to understand.

It can be solved directly by this formula. There is no need to add a calculated column.

Duration2 = 
VAR n1 =
    CALCULATE (
        MIN ( 'Table'[Sched_Start] ),
        FILTER ( 'Table', [date] = EARLIER ( 'Table'[date] ) )
    )
VAR n2 = [date] - 1
VAR n3 =
    CALCULATE (
        MAX ( 'Table'[Sched_Finsh] ),
        FILTER ( 'Table', [date] = EARLIER ( 'Table'[date2] ) )
    )
RETURN
    IF ( n3 = BLANK (), BLANK (), ( [date] + n1 ) - ( n2 + n3 ) )

Best Regards,

Community Support Team _Charlotte

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

[Qotsa]

@v-zhangti Thanks very much, that works. How could I remove the repeating times so it only shows the duration once ?

[v-zhangti · Accepted solution]

Hi, @Qotsa 

Duration =
VAR n1 =
    CALCULATE (
        MIN ( 'Table'[Sched_Start] ),
        FILTER ( 'Table', [date] = EARLIER ( 'Table'[date] ) )
    )
VAR n2 = [date] - 1
VAR n3 =
    CALCULATE (
        MAX ( 'Table'[Sched_Finsh] ),
        FILTER ( 'Table', [date] = EARLIER ( 'Table'[date2] ) )
    )
RETURN
    IF (
        n3 = BLANK (),
        BLANK (),
        IF ( [Sched_Start] = n1, ( [date] + n1 ) - ( n2 + n3 ), BLANK () )
    )

Best Regards,

Community Support Team _Charlotte

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

[Qotsa]

Fantastic. Thanks again.

[amitchandak]

@Qotsa , a new column  

var _datetime = [Date]+ [Sched_finish]
var _max = maxx(filter(Table, [Carter_id] = earlier([Carter_id]) && _datetime <[Date] +[Sched_start]),[Date]+ [Sched_finish])
return
[Date] +[Sched_start] - _max