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.
Hi everybody,
I'm looking for a solution, but I can't find it. I would like to know the time worked.
Info:
- People work in the morning, afternoon and night. Overtime is also possible.
- Thousands of posts are made in a day, that's thousands of time data. So there is not only a beginning and an end.
- Morning shift: 06-14 (can be overtime: 02-14, 06-18)
- Afternoon shift: 14-22 (can be overtime: 10-22, 14-02)
- Night shift: 22-06 (can be overtime: 18-06, 22-10)
time worked =
VAR balans =
CALCULATE (
MAX (EVENTHistory[hour]),
FILTER (
ALLSELECTED ( 'EVENTHistory' ),
EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker]
)
)
- CALCULATE (
MIN (EVENTHistory[hour]),
FILTER (
ALLSELECTED ( 'EVENTHistory' ),
EARLIER (EVENTHistory[worker]) = 'EVENTHistory'[worker]
)
)
RETURN
FORMAT ( INT ( balans * 24 ), "00" ) & ":"
& FORMAT ( INT ( MINUTE ( balans ) ), "00" ) & ":"
& FORMAT ( INT ( SECOND ( balans ) ), "00" )
Thank you in advance for your help.
Hi @Krivger - part of your problem is that you are tying to calculate the difference based on TIME data type. You need to consider changing to DATETIME data type. Please consider the following example:
The Time Difference calculated column in the first row cannot calculate the difference because the Time does not recognise the change in date. When the DateTime is captured, the time difference is correctly calculated.
Hi.
Unfortunately, your answer is not good. Normally it would be good, but in this case it is not good.
Time has shifted.
1 day = (example) 2022.10.16 6:00:00 -> 2022.10.17 6:00:00 = 24 hours
I tried to subtract 6 hours, but it wasn't good either. If I look at it for a week, it's even worse. I have no idea 😞
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
22 | |
21 | |
16 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |