Exclude Holiday, Weekend & Out of Office hours from Due Date

[Anonymous]

I have currently created a DueDate column using the below calculation:

DueDate = if List.Contains({"",null},[Target]) then null
                      else if List.Contains({0.5},[Target]) then [CreatedDate]+ #duration(0,0,30,0)
                       else [CreatedDate]+ #duration(0,[Target],0,0)

CreatedDate = Date/Time 

Target = 0.5, 1, 4, 8 hours

I also have a Date Table with Column IsWorking which identifies if a day is weekend or holiday (1 for working & 0 for non-working).

How can I

1) Incorporate IsWorking in my DueDate calculation?

2) Exclude non-work hours(working hours are 0900-1800) from the calculation?

[Anonymous]

@lbendlin Thank you for all your help. I havent had a chance to look at what you shared but will do so this week. Also will share what I did to find a solution. Thanks again!

[Anonymous]

@lbendlin any idea how to make this calculation?

[lbendlin]

Next step is to create masks with hour granularity* from your calendar table. Only working hours, excluding holidays. Make the masks a good bit wider than needed to have enough slack.  For example your task has 24 hours - that would mean three working days but there could be one or more weekends or holidays in there, so make the mask 15 days wide (for example).

Then overlay that mask over the task duration and calculate when the budget (mask) is expended. This is similar to a Pareto computation.

Well, at least that's the plan. Let's see if it actually can be implemented.

*) OR do you need half hourly granularity?

[lbendlin]

Here is a solution that does assume hourly granularity.  Work hours and work days are taken into account. Performance can certainly be improved

Hours function

(StartDate as datetime)=>
let
l = List.Generate(()=>0,each _ < 720,each _ + 1,each StartDate + #duration(0,_,0,0)),
f = List.Select(l,(li)=> Time.Hour(li)>8 and Time.Hour(li)<18 and List.Contains(Workdays[Date],Date.From(li)))
in 
f

FactTable

let
    Source = Excel.Workbook(File.Contents(Excel), null, true),
    FactTable_Sheet = Source{[Item="FactTable",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(FactTable_Sheet, [PromoteAllScalars=true]),
    #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"TicketNo", "CreatedDate", "CreatedTime", "ResolutionSLATarget(hrs)"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"CreatedDate", type number}, {"CreatedTime", type number}, {"ResolutionSLATarget(hrs)", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [CreatedDate],each [CreatedDate]+[CreatedTime]-1,Replacer.ReplaceValue,{"CreatedDate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"CreatedDate", type datetime}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type1",{"TicketNo", "CreatedDate", "ResolutionSLATarget(hrs)"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"ResolutionSLATarget(hrs)", "ResolutionSLATarget"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "SLA Timestamp", each List.Last( List.FirstN(Hours([CreatedDate]),[ResolutionSLATarget]+1)))
in
    #"Invoked Custom Function"

See attached. Ignore the DAX part - I broke that when reducing your problem to the essence.

[lbendlin]

Which country?  How many timezones?  Any regional (sub-country-level) holidays?

[Anonymous]

Just a single country(Turkey) with a single time zone(I use the system time GMT+2). There are no regional holidays only national. I have already have a calculaed column the identified if a day is a holiday, working day and it sits in the date table (created using DAX). Below is a sample of the table extract:

[lbendlin]

1. use INTERSECT

2. add another calendar table with hour level granularity.

Please provide sanitized sample data that fully covers your issue. Please post here as a table or use a file service. Screenshots of sample data are generally not useful.

[Anonymous]

hi @lbendlin ,

I tried to post as a table but I kept getting the message that it exceeds the word count.

I have upload the pbix and xls on my G-drive. Thank you for the help.

https://drive.google.com/drive/u/1/folders/15Rsm3LyzawHwjCuHQCrxARq1UpKqfIp1 

[lbendlin]

Thank you for providing the sample data. That helps a lot with proposing a potential solution.

Next question: What should the SLA calculation be when the ticket is created on a non-workday or on a workday but outside the 9-6 window?

[Anonymous]

hi @lbendlin 

The SLA exludes non-working hours, weekends(entire saturday & sunday) and holidays (entire day). So the calculation should excludes these hours.

I got to the point where I can calculate the due date which is calculated as per the target time. But I do not know how to incorporate the exclusions for non-working, weekend, holiday hours.

[lbendlin]

Can I assume that your created date for tickets 66676 and 68766 has a typo and should say April 22 2022 ?

[Anonymous]

hi @lbendlin 

It is a sample data. I intentionally kept them as 2021 to see the visualization for a year.

[lbendlin]

Your calendar table only covers 2022.

[Anonymous]

hi @lbendlin 

Yes, you are right. I have updated the file to include 2021 dates in the DateTable.