cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Date&Time differences between rows in same column

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.

Best regards,

Paul

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper I

## Re: Date&Time differences between rows in same column

I build following columns.

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

3 REPLIES 3
Highlighted
Helper I

## Re: Date&Time differences between rows in same column

I build following columns.

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

Highlighted
Community Support

## Re: Date&Time differences between rows in same column

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.

Highlighted
Helper I

## Re: Date&Time differences between rows in same column

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

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors