DAX Optimization - Business Hours Elapsed Calculated Column

[Rice]

Hello All!

I would appreciate your assistance with optimizing a calculated column. The goal is to calculate elapsed business hours between startdate and enddate.

Annoyance is that there's a potential for multiple business hour periods per weekday, so turns into a fun little challenge (at least for me).

I've put together a demo model for assistance. [Demo Model Download Link] 

Please see the calculated column BusinessHoursDurationTest for additional comments.

Thanks in advance!

Update: Made it slightly more efficient by separating the hours calculation between 1st day, lastday and middledays. Then added conditions to only 1stday and lastday to properly calculate the unique scenarios for each.

[OwenAuger]

Glad to hear it! You're welcome ☺️ 

I'll send you a separate message 🙂

[Rice]

Thanks for your time and answer, I'll do some validation to confirm, but initial testing proves very promising!

[OwenAuger · Accepted solution]

Hi @Rice 

Try this. It runs in well under a second for me in a test query.

BusinessHoursDurationTest-Owen =
VAR OneSecond = 1 / 3600 / 24
VAR StartDate =
    'SD - Incident Report Opened'[Date Link - Incident Submit Date]
VAR EndDate =
    'SD - Incident Report Opened'[Date Link - Support Centre Response Date]
VAR StartTime =
    'SD - Incident Report Opened'[Incident Submit Time]
VAR EndTime =
    'SD - Incident Report Opened'[Support Centre Response Time]
VAR StartDateTime = StartDate + StartTime
VAR EndDateTime = EndDate + EndTime
VAR DateRange =
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Date',
            "Date", 'Date'[Date],
            "Weekday Number", 'Date'[Day of Week Sort] + 0
        ),
        DATESBETWEEN ( 'Date'[Date], StartDate, EndDate )
    )
VAR BusinessHoursFiltered =
    -- Customer filter applied by context transition
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Business Hours',
            "Weekday Number", 'Business Hours'[Weekday Number] + 0,
            "Business Hours Start", 'Business Hours'[Business Hours Start],
            "Business Hours End", 'Business Hours'[Business Hours End]
        )
    )
VAR DateBusinessHours =
    NATURALLEFTOUTERJOIN ( DateRange, BusinessHoursFiltered )
VAR BusinessHoursCalculation =
    SUMX (
        DateBusinessHours,
        VAR CurrentBusinessHoursStart = [Date] + [Business Hours Start]
        VAR CurrentBusinessHoursEnd = [Date] + [Business Hours End]
        VAR EffectiveStart =
            MAX ( StartDateTime, CurrentBusinessHoursStart )
        VAR EffectiveEnd =
            MIN ( EndDateTime, CurrentBusinessHoursEnd ) + OneSecond -- Align with original calc based on discrete seconds
        RETURN
            ROUND (
                MAX ( BLANK ( ), EffectiveEnd - EffectiveStart ) * 24,
                3
            )
    )
RETURN
    BusinessHoursCalculation

Notes:

• The basic idea is to determine the StartDateTime and EndDateTime at the start, then determine the portion of each segment of Business Hours that lies within that range.
• I used NATURALLEFTOUTERJOIN to join DateRange to BusinessHoursFiltered, then iterate over that table.
• Didn't use 'Ticket Time Table' to count seconds, but rather calculated difference between datetime values.
• I added a second to the end time to align with your original calculation.

Regards,

Owen

[Rice]

Finally got around to validating this today. 

Exactly what I needed. Thank you for your assistance! 

Is there anywhere I can donate a tip to?