Adding Work Hours to Current Time (Considering Work Hours & Holidays)

[Anonymous]

I am trying to add a column with variable number of hours to the current time. I would like to consider work hours, work days and holidays in this calculation.

Work Hours: 7AM-12AM

Work Days: Monday-Saturday

Holidays: (see below)

HolidayList

01/01/20

05/29/20

07/03/20

09/07/20

11/26/20

12/25/20

Examples:

CurrentTime	WorkHours	CurrentTime + WorkHours	Notes
07/01/20 01:00PM	12	07/02/20 08:00AM	Work Day is 7:00AM-12:00AM. So +12H is next day at 8:00AM.
07/01/20 04:00AM	3	07/01/20 10:00AM	04:00AM is before the work day starts (7:00AM). So +3H from 07/01/2018 7:00AM is 07/01/2018 10:00AM
07/11/20 08:00PM	6	07/13/20 09:00AM	07/12/2020 is a Friday, so need to skip over Sunday to Monday's date 07/13/2020. +6H ends at 07/13/20 09:00AM.
07/03/20 09:00PM	7	07/06/20 11:00AM	07/04/20 is a Holiday and 07/05/2020 is a Sunday, so we need to skip to Monday 07/06/2020. +7H ends at 07/06/20 11:00AM.

In my table all the hours for CurrentTime will be the same, but just gave some examples to show the logic.

Any help is appreciated!

[v-lili6-msft]

hi  @Anonymous 

First, you need to define a calendar table and a holiday table.

Then create a calculate column as below:

Result = 
    VAR StartingDateTime = [CurrentTime]
    VAR TimeToAdd = TIME ( [WorkHours], 00, 0 )
     
    --
    --    These are parameters: working time is 09:00 - 17:00
    --
    VAR WorkTimeStart = TIME ( 07, 00, 00 )
    VAR WorkTimeEnd = TIME ( 23, 59, 00 ) + TIME ( 00, 01, 00 )
    VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )
     
    --
    --    Split StartingDateTime in two parts: date and time
    --
    VAR StartingTime = StartingDateTime - TRUNC ( StartingDateTime )
    VAR StartingDate = StartingDateTime - StartingTime
     
    --
    --    Check how many hours in the first day, how many hours in the last
    --  day and how many working days are needed in between
    --
    VAR FirstDayHours =
        MIN (
            MIN ( WorkTimeEnd - StartingTime, WorkingHours ),
            TimeToAdd 
        )
    VAR RemainingHours = TimeToAdd - FirstDayHours
    VAR RemainingDays = QUOTIENT ( RemainingHours, WorkingHours )
    VAR LastDayHours = RemainingHours - ( RemainingDays * WorkingHours )
     
    --
    --  Here we compute the end date, by taking the current working day
    --  number and searching for the date which happens to be on the 
    --  starting working day number + the number of working days
    --
    VAR StartingDayNumber =
        LOOKUPVALUE (
            'Date'[WorkingDayNumber],
            'Date'[Date], StartingDate 
        )
    VAR LastWorkingDayNumber =
        StartingDayNumber + RemainingDays 
        + 1 * ( LastDayHours > 0 )
    VAR LastDay =
        LOOKUPVALUE (
            'Date'[Date],
            'Date'[WorkingDayNumber], LastWorkingDayNumber
        )
     
    --
    --    Here we go, this is the result
    --
    VAR Result =IF(RemainingHours =0, LastDay + WorkTimeStart + FirstDayHours,
 LastDay + WorkTimeStart + LastDayHours)

RETURN
Result

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

@v-lili6-msft @OwenAuger 

Thank you for your replies, do you have any solution on how to handle cases where the time duration is greater than 24H? Right now, my values are being converted to time, so if I were to try to add +36H, it would instead only add +12H.

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

You may try to adjust it as this:

Result = 
    VAR StartingDateTime = [CurrentTime]
    VAR TimeToAdd = TIME ( [WorkHours], 00, 0 ) + + INT([WorkHours]/24)
     
    --
    --    These are parameters: working time is 09:00 - 17:00
    --
    VAR WorkTimeStart = TIME ( 07, 00, 00 )
    VAR WorkTimeEnd = TIME ( 23, 59, 00 ) + TIME ( 00, 01, 00 )
    VAR WorkingHours = ( WorkTimeEnd - WorkTimeStart )
     
    --
    --    Split StartingDateTime in two parts: date and time
    --
    VAR StartingTime = StartingDateTime - TRUNC ( StartingDateTime )
    VAR StartingDate = StartingDateTime - StartingTime
     
    --
    --    Check how many hours in the first day, how many hours in the last
    --  day and how many working days are needed in between
    --
    VAR FirstDayHours =
        MIN (
            MIN ( WorkTimeEnd - StartingTime, WorkingHours ),
            TimeToAdd 
        )
    VAR RemainingHours = TimeToAdd - FirstDayHours
    VAR RemainingDays = QUOTIENT ( RemainingHours, WorkingHours )
    VAR LastDayHours = RemainingHours - ( RemainingDays * WorkingHours )
     
    --
    --  Here we compute the end date, by taking the current working day
    --  number and searching for the date which happens to be on the 
    --  starting working day number + the number of working days
    --
    VAR StartingDayNumber =
        LOOKUPVALUE (
            'Date'[WorkingDayNumber],
            'Date'[Date], StartingDate 
        )
    VAR LastWorkingDayNumber =
        StartingDayNumber + RemainingDays 
        + 1 * ( LastDayHours > 0 )
    VAR LastDay =
        LOOKUPVALUE (
            'Date'[Date],
            'Date'[WorkingDayNumber], LastWorkingDayNumber
        )
     
    --
    --    Here we go, this is the result
    --
    VAR Result =IF(RemainingHours =0, LastDay + WorkTimeStart + FirstDayHours,
 LastDay + WorkTimeStart + LastDayHours)

RETURN
Result

Regards,

Lin

[OwenAuger]

HI @Anonymous 

There is a very good article here on this exact calculation:

https://www.sqlbi.com/blog/alberto/2019/03/25/using-dax-with-datetime-values/ 

The article describes both how to set up the Date table (with a flag and counter for work days) and the calculation itself.

Please post back if you need further help.

Regards,

Owen