Breaking Punch In and Punch Out DateTime columns into shift categories

Hi  dcrow5378 ,

Thanks for the reply from ToddChitt , please allow me to provide another insight:  

Here are the steps you can follow：

1. Create calculated column.

Day Hours =
VAR _daystart =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Day" ),
        [Start]
    )
VAR _datend =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Day" ),
        [End]
    )
VAR _value =
    SWITCH (
        TRUE (),
        AND (
            [In] >= _daystart
                && [In] < _datend,
            [Out] > _daystart
                && [Out] <= _datend
        ), [Out] - [In],
        AND ( [In] >= _daystart && [In] < _datend, [Out] > _datend ), _datend - _daystart
    )
RETURN
    IF (
        _value = BLANK (),
        BLANK (),
        VALUE ( LEFT ( _value, LEN ( _value ) - 2 ) )
            + DIVIDE ( VALUE ( RIGHT ( _value, 2 ) ), 60 )
    )

Evening Hours =
VAR _eveningstart =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Evening" ),
        [Start]
    )
VAR _eveningend =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Evening" ),
        [End]
    )
VAR _daystartday =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Day" ),
        [Start]
    )
VAR _dayendday =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Day" ),
        [End]
    )
VAR _value =
    SWITCH (
        TRUE (),
        AND ( [In] >= _eveningstart && [In] < _eveningend, [Out] <= _eveningend ), [Out] - [In],
        AND ( [In] >= _eveningstart && [In] < _eveningend, [Out] > _eveningend ), _eveningend - [In],
        AND ( [In] < _eveningstart, [Out] <= _eveningend && [Out] > _dayendday ), [Out] - _eveningstart
    )
RETURN
    IF (
        _value = BLANK (),
        BLANK (),
        VALUE ( LEFT ( _value, LEN ( _value ) - 2 ) )
            + DIVIDE ( VALUE ( RIGHT ( _value, 2 ) ), 60 )
    )

Night Hours =
VAR _nightstart =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Night" ),
        [Start]
    )
VAR _nightend =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Night" ),
        [End]
    )
VAR _eveningstart =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Evening" ),
        [Start]
    )
VAR _eveningend =
    MINX (
        FILTER ( ALL ( 'shift break times' ), 'shift break times'[Shift] = "Evening" ),
        [End]
    )
VAR _value =
    SWITCH (
        TRUE (),
        [In] >= _nightstart
            && [Out] <= _nightend,
            2400 - [In] + [Out],
        AND (
            [In] < _nightstart
                && [In] >= _eveningstart,
            [Out] >= _nightend
                && [Out] > _eveningend
        ), [Out] - _nightstart
    )
RETURN
    IF (
        _value = BLANK (),
        BLANK (),
        VALUE ( LEFT ( _value, LEN ( _value ) - 2 ) )
            + DIVIDE ( VALUE ( RIGHT ( _value, 2 ) ), 60 )
    )

2. 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