Expand the continuous date interval (Excluding weekends and holidays)

[v-yiruan-msft]

Scenario:

As I mentioned in my previous blog, we already have a date range, and when we need to assign metrics to each day, we need to expand the dates into a list of dates in days.

Then, if we need to consider working days and working hours, how to achieve it? I explain one method clearly in this blog. Hope it helps.

Table used:

Analyze:

The first step is to extend the time range which corresponds to each case to weekday time.

Then exclude the cases with holidays and weekends in the case date range.

Expected result:

Detailed steps:

1. Sample data

2. Construct List

In this case, the Start Time column contains not only the date, but also the time. As we know that the date with time is a decimal number when converted to a number, and the decimal number can't build the list. According to the sample data, [Start Time] is an integer except for the first row of each [ID] which is a decimal. The [End Time] is not an integer but can be obtained by converting the integer to time and subtracting 1 second from it, except for the last line which is a decimal.

So we construct a list of integers by rounding up and down, first removing the first and last, and then connecting the first and last individually with & to get the final list.

First and last value:

a={Number.RoundUp(Number.From([StartTime]))..Number.RoundDown(Number.From([End Time]))}

Intermediate values: 

b=List.Transform(List.Zip({{[Start Time=[Start Time]]}&List.Transform(a,each [Start Time=DateTime.From(_)-#duration(0,-9,0,0)]),List.Transform(a,each [End Time=DateTime.From(_)-#duration(0,6,0,0)])&{[End Time=[End Time]]}}),Record.Combine)

3. Delete columns

4. Expand List

5. Duplicate [Start Time] and convert it to Date type

6. Merge Holiday table to current table

7. Add custom column to determine the type of date

if [Holiday.Holidays Name] <> null then [Holiday.Holidays Name]
else if Date.DayOfWeek([Start Time])<6 and Date.DayOfWeek([Start Time])>0 then "workday"
else "weekend"

8. Filter non-working days

Through the above steps, we can exclude weekends and holidays to accurately view the actual working time of engineers for each case.

Author: Link Chen

Reviewer:  Kerry & Ula

Expand the continuous date interval (Excluding weekends and holidays).pbix