Forum Discussion

Krivger's avatar
Krivger
Regular Visitor
3 years ago

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.

 

 

 

 

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.

afteron shift
calculation
DAX
logic
Max
min
morning shift
Night shift
Power BI
time calculation

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • Krivger's avatar
      Krivger
      Regular Visitor
      3 years ago

      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 😞