Select the First & Last Door Access per Employee Id and Date Within Shift

[jemahusay]

Hi Everyone,

Good day!

Hoping to get some help on below problem.

I have a table containing multiple door accesses of employees in a period. I also have another table where it contains the shifts and the earliest allowed time in and latest allowed time in a shift. I want to capture the first & last door access that was made but should be within the earliest allowed time in and latest allowed time in. My problem now is with employees who are on the 3rd shift where they make their first door access on the current day and they make their last door access the following day. Also, there are some who makes door access earlier than the earliest allowed time in and makes door access beyond the latest allowed time in. Additional problem is that there are multiple door accesses in between. Below are my tables.

Door Accesses Table

Shift Table

Result Table

Thanks a lot in advance for the help.

Jerry

[Eric_Zhang · Accepted solution]

@jemahusay

I tried to identify the shift date for each door access rows with a calculate column as below and then find the min and max datetime in each shift. . See more details in the attached pbix.

SHIFT ON =
IF (
    HOUR ( yourTable[DATE] ) >= 17,
    DATEVALUE ( yourTable[DATE] ),
    IF (
        HOUR ( yourTable[DATE] ) <= 13,
        DATEVALUE ( yourTable[DATE] ) - 1,
        BLANK ()
    )
)

[haozhong]

Hey, where's the question? Maybe itemised what you want to achieve and we try to help.