Time Done within Shift (Starting Early or Finish Later)

[arythedj]

Hi,

I have a problem in calculating the duration done within the Shift.

Table 1 is Shift table:

Driver ID	Shif Start	Shift End
1	11/08/2022 09:00:00 AM	11/08/2022 05:00:00 PM
2	11/08/2022 09:00:00 AM	11/08/2022 05:00:00 PM
3	11/08/2022 09:00:00 AM	11/08/2022 05:00:00 PM
4	11/08/2022 08:00:00 PM	12/08/2022 01:00:00 AM

Table 2 is the Login Log (No relationship to Shift Table):

Driver ID	Login Start	Login End
1	11/08/2022 09:30:00 AM	11/08/2022 04:30:00 PM
1	11/08/2022 06:30:00 PM	11/08/2022 07:30:00 PM
2	11/08/2022 08:45:00 AM	11/08/2022 04:30:00 PM
3	11/08/2022 09:30:00 AM	11/08/2022 05:30:00 PM
4	11/08/2022 07:45:00 PM	12/08/2022 01:30:00 AM

I need a column in Table 1 that shows the Login Time within the shift so it will look like this:

Driver ID	Shif Start	Shift End	Login Time (hrs)
1	11/08/2022 09:00:00 AM	11/08/2022 05:00:00 PM	7
2	11/08/2022 09:00:00 AM	11/08/2022 05:00:00 PM	7.5
3	11/08/2022 09:00:00 AM	11/08/2022 05:00:00 PM	7.5
4	11/08/2022 08:00:00 PM	12/08/2022 01:00:00 AM	5

The tricky part is that the Driver can start earlier and/or finish later than the Shift Start/End.

Driver can also have multiple Login entries in the day.

So using this as filter in 'Calculate' will not work:

FILTER('Login Log', 'Login Log'[Driver ID] = Shift[Driver ID] && 'Login Log'[Login Start] >= Shift[Shift Start] && 'Login Log'[Login End] <= Shift[Shift End]))

Appreciate the help in advance.

[v-jianboli-msft · Accepted solution]

Hi @arythedj ,

Please try:

Login Time (hrs) =
VAR _ss =
    MAXX (
        FILTER ( 'Table 1', [Driver ID] = EARLIER ( 'Table 1'[Driver ID] ) ),
        [Shif Start]
    )
VAR _se =
    MAXX (
        FILTER ( 'Table 1', [Driver ID] = EARLIER ( 'Table 1'[Driver ID] ) ),
        [Shift End]
    )
VAR _a =
    ADDCOLUMNS (
        FILTER ( 'Table 2', [Driver ID] = EARLIER ( 'Table 1'[Driver ID] ) ),
        "diff1", DATEDIFF ( _ss, [Login Start], MINUTE ),
        "diff2", DATEDIFF ( _se, [Login End], MINUTE )
    )
VAR _b =
    ADDCOLUMNS (
        _a,
        "diff",
            IF (
                [Login End] <= _ss
                    || [Login Start] >= _se,
                0,
                SWITCH (
                    TRUE (),
                    [diff1] >= 0
                        && [diff2] <= 0, DATEDIFF ( [Login Start], [Login End], MINUTE ),
                    [diff1] >= 0
                        && [diff2] > 0, DATEDIFF ( [Login Start], _se, MINUTE ),
                    [diff1] < 0
                        && [diff2] <= 0, DATEDIFF ( _ss, [Login End], MINUTE ),
                    [diff1] < 0
                        && [diff2] > 0, DATEDIFF ( _ss, _se, MINUTE )
                )
            )
    )
RETURN
    SUMX ( _b, [diff] ) / 60

Output:

Best Regards,

Jianbo Li

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

[arythedj]

Thanks @v-jianboli-msft 

I forgot to mention that there can be multiple Shifts in a day that can be claimed by the same driver.

This Shift table also contains data across multiple days.

So the MAXX wouldn't work.

I actually have found the solution and here are the steps for anyone interested:

1. In Power Query, duplicate the Shift Table, then create a column that combine driver id and date

2. In Power Query (Login Log Table), create a column that combine driver id and date

3. Merge these 2 tables using the above columns

4. Exit Power Query

5. Now every shift for each driver and each day will have the login start and end of the driver for that day

6. We can now just compare if the Login Start/End is before or after Shift Start/End

7. Create 2 new columns Adjusted Login Start/End from point 5 above

8. Create a Duration column using DATEDIFF

9. Back to original Shift Table, we can SUM the Duration column 

[lbendlin]

You will need one more data point in the driver table - the shift the driver claims to be clocking in for. Or you need to set a threshold like "If they clock in within 30 minutes of the shift start they clock in for that shift" etc.  Same for the clocking out.

[arythedj]

Yes unfortunately there is no claiming shift or threshold.
Driver can freely loging in and loging out anytime.
Somehow I need a logic to get the overlap in their login log with the shift times.