Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
Hi @ThuJa23 ,
Based on your description, I have created a simple sample:
Please try:
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.
Hi @ThuJa23 ,
Based on your description, I have created a simple sample:
Please try:
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.
Hi @v-jianboli-msft ,
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
110 | |
109 | |
94 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |