cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

## Calculate working hours per day with check in and check out time

Hi all,

I have working hours data for 1 employee as below

with TerminalereignistypID is 1: check in, and 2: check out

The target working hours per day is 8:45

The break time is always fixed 45 minutes

I would like to calculate on the same day, how many hours an employee works by taking time from TerminalereignistypID  2 minus TerminalereignistypID  1 and minus also the fixed break time (0,45)

There are also some days, that the employee check out in the middle of day, and check in again as example from date 16.11.2022, if the break time between check out and check in again is greater than (>) 45 mins, then no more minus 0,45, but if it less than 45 mins, then minus also the rest, for ex: if self - break time is 30 mins, so in calculation of working hours just needs to minus more 15 mins (to reach total 45mins of break time).

In the example of 16.11.2022, from first check out to check in again took 1,50, it overs than 0,45, so in this case just calculate the sum of slot 1 working time (6:33 - 11:34) 5,01 and slot 2 (13:25 - 15:40) 2,15, sum of working hours this day is 7,16 (without minus 45 mins break time)

Please help me how should I do the DAX measure in this case to calculate the working hours rightly?

Thanks so much for your helps

Thu

1 ACCEPTED SOLUTION
Community Support

Hi @ThuJa23 ,

Based on your description, I have created a simple sample:

``````Measure =
VAR _a =
SUMX (
'Table',
IF (
[TerminalereignistypID] = 2,
DATEDIFF (
CALCULATE (
MAX ( 'Table'[Time] ),
FILTER (
'Table',
[TerminalereignistypID] = 1
&& [Time] < EARLIER ( 'Table'[Time] )
&& [Date] = EARLIER ( 'Table'[Date] )
)
),
[Time],
MINUTE
)
)
)
VAR _b =
SUMX (
'Table',
IF (
[TerminalereignistypID] = 1,
DATEDIFF (
CALCULATE (
MAX ( 'Table'[Time] ),
FILTER (
'Table',
[Date] = EARLIER ( 'Table'[Date] )
&& [TerminalereignistypID] = 2
&& [Time] < EARLIER ( 'Table'[Time] )
)
),
[Time],
MINUTE
)
)
)
VAR _c =
IF ( _b < 45, 45, 0 )
VAR _d = ( _a - _c ) / 60
RETURN
INT ( _d )
+ 0.01
* MOD ( _a - _c, 60 )
``````

Final 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.

2 REPLIES 2
Community Support

Hi @ThuJa23 ,

Based on your description, I have created a simple sample:

``````Measure =
VAR _a =
SUMX (
'Table',
IF (
[TerminalereignistypID] = 2,
DATEDIFF (
CALCULATE (
MAX ( 'Table'[Time] ),
FILTER (
'Table',
[TerminalereignistypID] = 1
&& [Time] < EARLIER ( 'Table'[Time] )
&& [Date] = EARLIER ( 'Table'[Date] )
)
),
[Time],
MINUTE
)
)
)
VAR _b =
SUMX (
'Table',
IF (
[TerminalereignistypID] = 1,
DATEDIFF (
CALCULATE (
MAX ( 'Table'[Time] ),
FILTER (
'Table',
[Date] = EARLIER ( 'Table'[Date] )
&& [TerminalereignistypID] = 2
&& [Time] < EARLIER ( 'Table'[Time] )
)
),
[Time],
MINUTE
)
)
)
VAR _c =
IF ( _b < 45, 45, 0 )
VAR _d = ( _a - _c ) / 60
RETURN
INT ( _d )
+ 0.01
* MOD ( _a - _c, 60 )
``````

Final 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.

So sorry for my late replies.

It works well, just we have more conditions in breaking time for employees.

For example:

- if the employees work more or equal than 6 hours, then just minus 30 mins for lunch break

- if the employees work more than 9 hours, then minus 45 mins (included breakfast break 15mins, and lunch break 45 mins)

I am trying to test more with the calculation based on your formular.

But thanks so much for your helps, it helps me to understand the structure defintely better

Best regards,

Thu

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors