Date&Time differences between rows in same column

[Anonymous]

Hello ladies and gentlemen,

once again I need your help. 

I have following challenge.

I have a time and attendance table which gives me the login data of an employee (Workerpersonallnumber) per day. I have a column where i see whether the employee logged in or logged out. 

The expected outcome is to have a duration of logged in hours per employee per day/shift.

I tried to solve it with two different tables - one signin and one signout one - but i didnt get a connection that would link those two.

Your help is as always highly appreciated.

Thanks in advance.

Best regards,

Paul

[v-zhenbw-msft]

Hi @Anonymous ,

We can create a date column and a measure to meet your requirement.

1. Create a date column in table.

Date = DATE(YEAR('Table'[StartDateTime]),MONTH('Table'[StartDateTime]),DAY('Table'[StartDateTime]))

2. Then we can create a measure like this,

Hours = 
VAR sign_in =
    CALCULATE (
        MIN ( 'Table'[StartDateTime] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Type] = "signin"
                && 'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[WorkerNumber] = MAX ( 'Table'[WorkerNumber] )
        )
    )
VAR sign_out =
    CALCULATE (
        MAX ( 'Table'[StartDateTime] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Type] = "signOut"
                && 'Table'[Date] = MAX ( 'Table'[Date] )
                && 'Table'[WorkerNumber] = MAX ( 'Table'[WorkerNumber] )
        )
    )
RETURN
DATEDIFF ( sign_in, sign_out, HOUR )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

BTW, pbix as attached.       

Best regards,

Community Support Team _ zhenbw

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

[Anonymous]

Not all heroes were capes....  thank you very much. It works perfect. 

[Anonymous · Accepted solution]

I build following columns.

How do i get PowerBI to calculate chronologically the duration between each sign in and sign out by employee?