How to get min & max value of previous or next day?

[Jaed]

Hi All,

need help with this challenge. The company I work at has a three shift modell.

We track all actions in our system.

The first action of an employee is his starting time and the last action his finish time. 

I have used the following dax formular:

Start Work = 
CALCULATE(
    MIN(Sheet1[Date & Time from action]),
        ALLEXCEPT(
            Sheet1,
            Sheet1[User],
            Sheet1[Date]
        )
    
)

Finish Work = 
CALCULATE(
    MAX(Sheet1[Date & Time from action]),
        ALLEXCEPT(
            Sheet1,
            Sheet1[User],
            Sheet1[Date]
        )
    
)

We are having issue getting the correct date and times when an employee starts working on day one and finishes work on day two.

The third shift starts at 10 pm and ends at 8 am.

Currently Power BI showes me the following:

What we would like to have in each row is:

Start work = 21.05.2024 22:17:44

Finish Work = 22.05.2024 05:49:27

Sample Data can be found here https://drive.google.com/file/d/1XyEqwMgfHecjkpWswZXv_jn3FCJf3-dP/view?usp=drive_link

Much obliged for looking into this matter

Best regards

[hackcrr · Accepted solution]

Hi, @Jaed 

Below is the updated DAX formula for calculating Start Work and Finish Work to properly account for shifts that span two days.

If shifts start on day 1 and end on day 2, we need to add logic to specifically handle this situation. Assuming the shift times are correctly recorded in your data, the following adjustments can be made:

Start Work Adjusted = 
VAR StartDateTime = 
    CALCULATE(
        MIN(Sheet1[Date & Time from action]),
        ALLEXCEPT(
            Sheet1,
            Sheet1[User],
            Sheet1[Date],
            Sheet1[Shift]
        )
    )

RETURN 
    IF(
        HOUR(StartDateTime) >= 22,
        DATEADD(StartDateTime, 1, DAY),
        StartDateTime
    )

Finish Work Adjusted = 
VAR FinishDateTime = 
    CALCULATE(
        MAX(Sheet1[Date & Time from action]),
        ALLEXCEPT(
            Sheet1,
            Sheet1[User],
            Sheet1[Date],
            Sheet1[Shift]
        )
    )

RETURN 
    IF(
        HOUR(FinishDateTime) <= 8,
        DATEADD(FinishDateTime, -1, DAY),
        FinishDateTime
    )

These adjusted measures should ensure that the start and end times of shifts spanning two days are correctly calculated.

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...