Working Hours including Weekends but with different times

[el_corto]

Hi All,

Wondering for some guidance here, I have searched Google far and wide and look in forums and I can't find anything to fully help me complete what I'm after so wondering if anyone on here can help and guide me.

I have currently have data pulled from Sharepoint for Start Times and End Times, I've been asked to calcualte how long it takes for an task to be completed but only during business hours, this I can find easily on youtube and google but the complex part is including weekends and bank holidays as we still operate then but on a small time frame.

I've got some code to work no problem with 8am - 8pm every day but this wouldn't work for what is needed so created some new code but this doesn't work as intended either so wondering if someone can just pick it apart and tell whats wrong?

I have a query table set up with the data below which has the working hours for each day (0 = Monday)

Removing the not needed data, this is then broken apart for Date Start / Finish and Time Start / Finish but each time I get the calculate of 0:00 if done on the same day or 12:00 if over 1 day.

This is the code below I have used to try and resolve the issue, I found the code from another site but doesn't work at all how I wanted it to and tried lots of tweaks but never changes the outcome required.

Working Hours Between Dates = 
var startDate = 'Hub SOS Raw'[DateCaseStarted]
var startTime = 'Hub SOS Raw'[TimeCaseStarted] - startDate
var endDate = 'Hub SOS Raw'[DateCaseComplete]
var endTime = 'Hub SOS Raw'[TimeCaseComplete] - endDate

var firstFullDay = startDate + 1
var lastFullDay = endDate - 1

var inBetweenWorkingHours = 
    IF(
        firstFullDay > lastFullDay,
        0,
        SUMX(CALENDAR(firstFullDay, lastFullDay), LOOKUPVALUE(WorkingHoursTable[WorkingHoursInAllDay], WorkingHoursTable[WeekDay], WEEKDAY('Hub SOS Raw'[DateCaseStarted], 2)))
    )

var firstDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))
var firstDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(startDate, 2))

var lastDayStart = LOOKUPVALUE(WorkingHoursTable[StartTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))
var lastDayEnd = LOOKUPVALUE(WorkingHoursTable[EndTime], WorkingHoursTable[WeekDay], WEEKDAY(endDate, 2))

var effectiveStartTime = IF(startTime < firstDayStart, firstDayStart, startTime)
var effectiveEndTime = IF(endTime > lastDayEnd, lastDayEnd, endTime)
        
return 
    IF(
        startDate = endDate,
        24 * IF(effectiveEndTime > effectiveStartTime, effectiveEndTime - effectiveStartTime, 0),
        var firstDayWorkingHour = 
            24 *
            IF(
                startTime > firstDayEnd,
                0,
                firstDayEnd - effectiveStartTime
            )

        var lastDayWorkingHour = 
            24 *
            IF(
                endTime < lastDayStart,
                0,
                effectiveEndTime - lastDayStart
            )
        
        return firstDayWorkingHour + lastDayWorkingHour + inBetweenWorkingHours
    )

[v-yingjl]

Hi @el_corto ,

So what is your expected output in this thread based on your description? You can consider sharing more details about it.

Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

I think I'd approach it slightly differently so there aren't as many different cases to work through.

Try something like this:

Working Hours =
VAR startDate = 'Hub SOS Raw'[DateCaseStarted]
VAR startTime = 'Hub SOS Raw'[TimeCaseStarted]

VAR endDate = 'Hub SOS Raw'[DateCaseComplete]
VAR endTime = 'Hub SOS Raw'[TimeCaseComplete]

VAR firstDayEnd  = LOOKUPVALUE ( WHT[EndTime], WHT[WeekDay], WEEKDAY ( startDate, 2 ) )
VAR lastDayStart = LOOKUPVALUE ( WHT[StartTime], WHT[WeekDay], WEEKDAY ( endDate, 2 ) )

VAR fullDays =
    FILTER (
        CALENDAR ( startDate, endDate ),
        [Date] > startDate && [Date] < endDate
    )
VAR fullDaysTime =
    SUMX (
        fullDays,
        LOOKUPVALUE ( WHT[WorkingHoursInAllDay], WHT[WeekDay], WEEKDAY ( [Date], 2 ) )
    )

VAR firstDayTime = firstDayEnd - startTime
VAR lastDayTime = endTime - lastDayStart

RETURN
    IF (
        startDate = endDate,
        endTime - startTime,
        firstDayTime + fullDaysTime + lastDayTime
    ) * 24