cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Krivger
Regular Visitor

Night shift (logic, calculation)

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)

MIN - MAX works in the morning and in the afternoon, because the time data comes one after the other. It does not work at night because it is separate, e.g.: 0:00 (start) -> (end) 6:00 + 22:00 (start) -> (end) 0:00. This is a MIN - MAX 24h.

 

Krivger_0-1668158462675.png

 

 

 

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.

2 REPLIES 2
Daryl-Lynch-Bzy
Super User
Super User

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:

DarylLynchBzy_0-1668187462571.png

 

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 😞

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors