cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arythedj
Helper I
Helper I

Time Done within Shift (Starting Early or Finish Later)

Hi,

 

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

 

Table 1 is Shift table:

Driver IDShif StartShift End
111/08/2022 09:00:00 AM11/08/2022 05:00:00 PM
211/08/2022 09:00:00 AM11/08/2022 05:00:00 PM
311/08/2022 09:00:00 AM11/08/2022 05:00:00 PM
411/08/2022 08:00:00 PM12/08/2022 01:00:00 AM

 

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

Driver IDLogin StartLogin End
111/08/2022 09:30:00 AM11/08/2022 04:30:00 PM
111/08/2022 06:30:00 PM11/08/2022 07:30:00 PM
211/08/2022 08:45:00 AM11/08/2022 04:30:00 PM
311/08/2022 09:30:00 AM11/08/2022 05:30:00 PM
411/08/2022 07:45:00 PM12/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 IDShif StartShift EndLogin Time (hrs)
111/08/2022 09:00:00 AM11/08/2022 05:00:00 PM7
211/08/2022 09:00:00 AM11/08/2022 05:00:00 PM7.5
311/08/2022 09:00:00 AM11/08/2022 05:00:00 PM7.5
411/08/2022 08:00:00 PM12/08/2022 01:00:00 AM5

 

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.
1 ACCEPTED SOLUTION
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1660807493321.png

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.

View solution in original post

4 REPLIES 4
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1660807493321.png

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.

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
Super User
Super User

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.

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.

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors