DAX to calculate night shift hours spanning 10pm to 8am

[Shr88]

I'm working with a dataset that includes agents' working times, recorded as start_datetime and end_datetime. I'm trying to create a measure that calculates the total hours worked within the night shift window of 10 PM to 8 AM. If anyone has ideas or suggestions on how to approach this, I’d greatly appreciate your input!

[v-karpurapud]

Hi @Shr88 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-karpurapud]

Hi @Shr88 

Thank you for reaching out to the Microsoft Fabric Community Forum.

Regarding your query on creating DAX measure to calculate the number of hours worked within the night shift window (10 PM to 8 AM).

I’m not fully aware of the exact structure of your dataset, I’ve created a sample .pbix file to demonstrate one possible solution.

Please try the below DAX:

Night Shift Hours = 
SUMX (
    'Shifts',
    VAR StartDT = 'Shifts'[start_datetime]
    VAR EndDT = 'Shifts'[end_datetime]
    VAR ShiftStart = StartDT
    VAR ShiftEnd = EndDT
    VAR StartDate = DATEVALUE(ShiftStart)
    VAR PrevDate = StartDate - 1
    VAR EndDate = DATEVALUE(ShiftEnd)
    VAR TenPM = TIME(22, 0, 0)
    VAR EightAM = TIME(8, 0, 0)
    VAR NightStart1 = StartDate + TenPM
    VAR NightEnd1 = StartDate + 1 + EightAM
    
    VAR NightStart0 = PrevDate + TenPM
    VAR NightEnd0 = StartDate + EightAM
    VAR Overlap1_Start = MAX(ShiftStart, NightStart1)
    VAR Overlap1_End = MIN(ShiftEnd, NightEnd1)
    VAR Overlap1 =
        IF(Overlap1_End > Overlap1_Start, DATEDIFF(Overlap1_Start, Overlap1_End, MINUTE), 0)
    VAR Overlap0_Start = MAX(ShiftStart, NightStart0)
    VAR Overlap0_End = MIN(ShiftEnd, NightEnd0)
    VAR Overlap0 =
        IF(Overlap0_End > Overlap0_Start, DATEDIFF(Overlap0_Start, Overlap0_End, MINUTE), 0)
    RETURN
        (Overlap0 + Overlap1) / 60
)

I’ve attached a snapshot and the .pbix file demonstrating this logic. Please review them to see if this solution aligns with your requirements.

        

If this doesn’t fully meet your needs, could you kindly share a sample of your data and more detailed context? That would help us provide a more accurate solution.
 

If this response resolves your query, kindly mark it as Accepted Solution to help other community members. A Kudos is also appreciated if you found the response helpful.
 

Thank You!

[Shr88]

Hi @v-karpurapud thank you so much for your response and apologies for the delay in getting back to you. The below seems to be doing what I'm expecting it to do.

night_shift_hours =

SUMX (

    'table',

    VAR StartDateTime = 'table'[start_datetime]

    VAR EndDateTime   = 'table'[end_datetime]

    VAR IsConsecutive = 'table'[is_consecutive]

    VAR StartDate     = DATEVALUE(StartDateTime)

    VAR EndDate       = DATEVALUE(EndDateTime)

    VAR Midnight      = TIME(0, 0, 0)

    VAR EightAM       = TIME(8, 0, 0)

    VAR TenPM         = TIME(22, 0, 0)

    VAR EndOfDay      = TIME(23, 59, 59)

    VAR IsLastDayOfMonth = StartDate = EOMONTH(StartDateTime, 0)

    VAR IsFirstDayOfMonth = DAY(StartDateTime) = 1

    // -------------------

    // Same-day calculations (StartDate = EndDate)

    // -------------------

    VAR SameDayCalc =

        IF (

            IsFirstDayOfMonth && IsConsecutive,

            // If first day of month and consecutive, use one set of rules…

            SWITCH (

                TRUE(),

                StartDateTime >= StartDate + Midnight && EndDateTime < EndDate + EightAM,

                    DATEDIFF(StartDate + Midnight, StartDateTime, MINUTE)

                    + DATEDIFF(StartDateTime, EndDateTime, MINUTE),

                StartDateTime >= StartDate + Midnight && EndDateTime >= EndDate + EightAM,

                    DATEDIFF(StartDate + Midnight, StartDateTime, MINUTE)

                    + DATEDIFF(StartDateTime, EndDate + EightAM, MINUTE),

                BLANK()  // Default if none of the above conditions match

            ),

            // Otherwise, use this set of rules for same-day calculations

            SWITCH (

                TRUE(),

                // Case: shift completely between midnight and 8 AM

                StartDateTime >= StartDate + Midnight && EndDateTime < EndDate + EightAM,

                    DATEDIFF(StartDateTime, EndDateTime, MINUTE),

                // Case: shift starts between midnight and 8 AM and ends after 8 AM but before 10 PM

                StartDateTime >= StartDate + Midnight && StartDateTime < StartDate + EightAM &&

                    EndDateTime >= EndDate + EightAM && EndDateTime < EndDate + TenPM,

                    DATEDIFF(StartDateTime, EndDate + EightAM, MINUTE),

                // Case: shift entirely in the day (8 AM to 10 PM) – no night hours, return BLANK

                StartDateTime >= StartDate + EightAM && EndDateTime < EndDate + TenPM,

                    BLANK(),

                // Case: shift spans part of the evening

                StartDateTime < StartDate + TenPM && EndDateTime <= EndDate + EndOfDay,

                    DATEDIFF(StartDate + TenPM, EndDateTime, MINUTE),

                // Case: shift entirely after 10 PM (up to midnight)

                StartDateTime >= StartDate + TenPM && EndDateTime <= EndDate + EndOfDay,

                    DATEDIFF(StartDateTime, EndDateTime, MINUTE),

                BLANK()  // Default

            )

        )

    // -------------------

    // Next-day calculations (StartDate = EndDate - 1)

    // -------------------

    VAR NextDayCalc =

        IF (

            IsLastDayOfMonth,

            // Case for last day of the month

            SWITCH (

                TRUE(),

                // Case: shift starts before 8 AM on the first day and ends after midnight on next day

                StartDateTime < StartDate + EightAM && EndDateTime >= EndDate + Midnight,

                    DATEDIFF(StartDateTime, StartDate + EightAM, MINUTE)

                    + DATEDIFF(StartDate + TenPM, EndDate + Midnight, MINUTE),

                // Case: shift starts between 8 AM and 10 PM on first day and ends after midnight 

                StartDateTime >= StartDate + EightAM && StartDateTime < StartDate + TenPM && EndDateTime >= EndDate + Midnight,

                    DATEDIFF(StartDate + TenPM, EndDate + Midnight, MINUTE),

                // Case: shift starts after 10 PM on first day and ends after midnight

                StartDateTime >= StartDate + TenPM && EndDateTime >= EndDate + Midnight,

                    DATEDIFF(StartDateTime, EndDate + Midnight, MINUTE),

                BLANK()  // Default

            ),

            // Not the last day of the month – alternative rules

            SWITCH (

                TRUE(),

                // Case: shift starts before 10 PM on day 1 and ends before 8 AM on day 2

                StartDateTime < StartDate + TenPM && EndDateTime < EndDate + EightAM,

                    DATEDIFF(StartDate + TenPM, EndDate + Midnight, MINUTE)

                    + DATEDIFF(EndDate + Midnight, EndDateTime, MINUTE),

                // Case: shift starts before 10 PM on day 1 and ends at or after 8 AM on day 2

                StartDateTime < StartDate + TenPM && EndDateTime >= EndDate + EightAM,

                    DATEDIFF(StartDate + TenPM, EndDate + Midnight, MINUTE)

                    + DATEDIFF(EndDate + Midnight, EndDate + EightAM, MINUTE),

                // Case: shift starts after 10 PM on day 1 and ends before 8 AM on day 2

                StartDateTime >= StartDate + TenPM && EndDateTime < EndDate + EightAM,

                    DATEDIFF(StartDateTime, EndDate + Midnight, MINUTE)

                    + DATEDIFF(EndDate + Midnight, EndDateTime, MINUTE),

                // Case: shift starts after 10 PM on day 1 and ends at or after 8 AM on day 2

                StartDateTime >= StartDate + TenPM && EndDateTime >= EndDate + EightAM,

                    DATEDIFF(StartDateTime, EndDate + Midnight, MINUTE)

                    + DATEDIFF(EndDate + Midnight, EndDate + EightAM, MINUTE),

                BLANK()  // Default

            )

        )

    

    VAR TotalMinutes =

        IF (

            StartDate = EndDate,

            SameDayCalc,

            IF (StartDate = EndDate - 1, NextDayCalc, BLANK())

        )

    

    RETURN

        TotalMinutes / 60

)

[v-karpurapud]

Hi @Shr88 

Thank you for sharing your detailed approach. Since you're open to a more concise alternative, here’s an optimized version of your DAX that achieves the same result while simplifying the logic.

night_shift_hours = 
SUMX (
    'table',
    VAR StartDT = 'table'[start_datetime]
    VAR EndDT = 'table'[end_datetime]

    VAR StartDate = DATEVALUE(StartDT)
    VAR PrevDate = StartDate - 1

    VAR TenPM = TIME(22, 0, 0)
    VAR EightAM = TIME(8, 0, 0)

    // Define current and previous night shift windows
    VAR NightStartPrev = PrevDate + TenPM
    VAR NightEndPrev   = StartDate + EightAM

    VAR NightStartCurr = StartDate + TenPM
    VAR NightEndCurr   = StartDate + 1 + EightAM

    // Overlap with previous night (for shifts starting early in the morning)
    VAR OverlapPrevStart = MAX(StartDT, NightStartPrev)
    VAR OverlapPrevEnd   = MIN(EndDT, NightEndPrev)

    VAR OverlapPrev =
        IF(OverlapPrevEnd > OverlapPrevStart,
           DATEDIFF(OverlapPrevStart, OverlapPrevEnd, MINUTE),
           0)

    // Overlap with current night shift
    VAR OverlapCurrStart = MAX(StartDT, NightStartCurr)
    VAR OverlapCurrEnd   = MIN(EndDT, NightEndCurr)

    VAR OverlapCurr =
        IF(OverlapCurrEnd > OverlapCurrStart,
           DATEDIFF(OverlapCurrStart, OverlapCurrEnd, MINUTE),
           0)

    RETURN
        (OverlapPrev + OverlapCurr) / 60
)

If this post helps, kindly please Accept the Solution.

Thank You!

[Shr88]

Thanks @v-karpurapud this solution is working remarkably well for the most part! However, I have a request regarding the DAX calculations during month-end.

Let me provide an example to clarify:

In the screenshot above, the first row shows that the agent worked from March 31st at 11:45 PM to April 1st at 4:00 AM. Given that March 31st is the final day of the month, I would like the DAX to calculate the night shift hours from March 31st at 11:45 PM to April 1st at 12:00 AM, resulting in a total of 0.25 hours for that first entry.

Additionally, in the second row where the work starts on April 1st at 4:00 AM, the agent essentially worked from midnight to 4:40 AM, which should account for 4.67 night shift hours.

I have developed a workaround for this specific scenario, but I would love to hear your thoughts on how you might tackle it.

[v-karpurapud · Accepted solution]

Hi @Shr88 

Please try the below DAX:

night_shift_hours = 
SUMX (
    'Shifts',
    VAR StartDT = 'Shifts'[start_datetime]
    VAR EndDT = 'Shifts'[end_datetime]
    
    VAR StartDate = DATEVALUE(StartDT)
    VAR PrevDate = StartDate - 1

    VAR TenPM = TIME(22, 0, 0)
    VAR EightAM = TIME(8, 0, 0)
    VAR Midnight = TIME(0, 0, 0)

    VAR IsMonthEnd = DAY(StartDate) = DAY(EOMONTH(StartDate, 0))

    // Month-end window: 10 PM to 12 AM of same day
    VAR MonthEndWindowStart = StartDate + TenPM
    VAR MonthEndWindowEnd = StartDate + 1  // Midnight of next day

    // Fix: allow month-end overlap even if StartDT < 10 PM
    VAR MonthEndOverlapStart = MAX(StartDT, MonthEndWindowStart)
    VAR MonthEndOverlapEnd   = MIN(EndDT, MonthEndWindowEnd)

    VAR MonthEndDuration =
        IF(
            IsMonthEnd && MonthEndOverlapEnd > MonthEndOverlapStart,
            DATEDIFF(MonthEndOverlapStart, MonthEndOverlapEnd, MINUTE),
            0
        )

    // Standard shift windows
    VAR NightStartPrev = PrevDate + TenPM
    VAR NightEndPrev   = StartDate + EightAM

    VAR NightStartCurr = StartDate + TenPM
    VAR NightEndCurr   = StartDate + 1 + EightAM

    VAR UseStandard = NOT IsMonthEnd

    VAR OverlapPrev =
        IF(
            UseStandard,
            VAR Start1 = MAX(StartDT, NightStartPrev)
            VAR End1   = MIN(EndDT, NightEndPrev)
            RETURN IF(End1 > Start1, DATEDIFF(Start1, End1, MINUTE), 0),
            0
        )

    VAR OverlapCurr =
        IF(
            UseStandard,
            VAR Start2 = MAX(StartDT, NightStartCurr)
            VAR End2   = MIN(EndDT, NightEndCurr)
            RETURN IF(End2 > Start2, DATEDIFF(Start2, End2, MINUTE), 0),
            0
        )

    RETURN
        DIVIDE(MonthEndDuration + OverlapPrev + OverlapCurr, 60)
)

I hope this meets your requirement. Please let me know if you need further assistance.

If this post helps, kindly mark it as Accepted Solution. Appreciate your Kudos.

Thank You!

[Shr88]

Thanks @v-karpurapud. This post is definitely helpful. However, the night shift hours in the second row should be 4.67 instead of 0.67. This adjustment is necessary because we need to account for any working hours from the beginning of the month if they exist.

In this case, we correctly considered the working hours between 31 March 11:45 PM and 1 April 4 AM, which amounts to 0.25 hours. However, since the agent worked across the end of the previous month into the first day of the current month, the night shift hours should be calculated from 1 April 12 AM to 1 April 4:40 AM, totaling 4.67 hours.

I'm happy to accept your last post as a solution, since it has definitely helped my requirement and I've managed to fix the issue that I've mentioned above.

[lbendlin]

Are you interested in a more concise solution or is yours "good enough" ?

[Shr88]

Sure, I'd interested further optimising this DAX if there's a possibility. If not, I'd be happy to accept my post as the solution.

[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. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[lbendlin]

Does your data have both date and time columns or does it have timestamp (datetime) columns for these shift details?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Shr88]

Nevermind, this is what I came up with and it seems to be doing what I want it to.

night_shift_hours =
SUMX (
'table',
VAR StartDateTime = 'table'[start_datetime]
VAR EndDateTime = 'table'[end_datetime]
VAR StartDate =
DATEVALUE ( StartDateTime )
VAR EndDate =
DATEVALUE ( EndDateTime )
VAR Midnight =
TIME ( 0, 0, 0 )
VAR EightAM =
TIME ( 8, 0, 0 )
VAR TenPM =
TIME ( 22, 0, 0 )
VAR EndOfDay =
TIME ( 23, 59, 59 )
VAR Duration =
IF (
// Case: StartDate = EndDate
StartDate = EndDate,
SWITCH (
TRUE (),
// Case 1: start_datetime >= midnight and end_datetime < 8 AM
StartDateTime >= StartDate + Midnight
&& EndDateTime < EndDate + EightAM, DATEDIFF ( StartDateTime, EndDateTime, MINUTE ),
// Case 2: start_datetime >= midnight and end_datetime >= 8 AM
StartDateTime >= StartDate + Midnight
&& StartDateTime < StartDate + EightAM
&& EndDateTime >= EndDate + EightAM
&& EndDateTime < EndDate + TenPM, DATEDIFF ( StartDateTime, EndDate + EightAM, MINUTE ),
// Case 3: start_datetime >= 8 AM and end_datetime < 10 PM
StartDateTime >= StartDate + EightAM
&& EndDateTime < EndDate + TenPM, BLANK (),
// Case 4: start_datetime < 10 PM and end_datetime <= midnight
StartDateTime < StartDate + TenPM
&& EndDateTime <= EndDate + EndOfDay, DATEDIFF ( StartDate + TenPM, EndDateTime, MINUTE ),
// Case 5: start_datetime >= 10 PM and end_datetime <= midnight
StartDateTime >= StartDate + TenPM
&& EndDateTime <= EndDate + EndOfDay, DATEDIFF ( StartDateTime, EndDateTime, MINUTE ),
BLANK () // Default case
),
IF (
// Case: StartDate = EndDate - 1
StartDate = EndDate - 1,
SWITCH (
TRUE (),
// Case 6: start_datetime < 10 PM and end_datetime < 8 AM
StartDateTime < StartDate + TenPM
&& EndDateTime < EndDate + EightAM,
DATEDIFF ( StartDate + TenPM, EndDate + Midnight, MINUTE )
+ DATEDIFF ( EndDate + Midnight, EndDateTime, MINUTE ),
// Case 7: start_datetime < 10 PM and end_datetime >= 8 AM
StartDateTime < StartDate + TenPM
&& EndDateTime >= EndDate + EightAM,
DATEDIFF ( StartDate + TenPM, EndDate + Midnight, MINUTE )
+ DATEDIFF ( EndDate + Midnight, EndDate + EightAM, MINUTE ),
// Case 8: start_datetime >= 10 PM and end_datetime < 8 AM
StartDateTime >= StartDate + TenPM
&& EndDateTime < EndDate + EightAM,
DATEDIFF ( StartDateTime, EndDate + Midnight, MINUTE )
+ DATEDIFF ( EndDate + Midnight, EndDateTime, MINUTE ),
// Case 9: start_datetime >= 10 PM and end_datetime >= 8 AM
StartDateTime >= StartDate + TenPM
&& EndDateTime >= EndDate + EightAM,
DATEDIFF ( StartDateTime, EndDate + Midnight, MINUTE )
+ DATEDIFF ( EndDate + Midnight, EndDate + EightAM, MINUTE ),
BLANK ()
),
BLANK () // Default case for other conditions
)
)
RETURN
Duration / 60
)