Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

v-yiruan-msft

Expand the continuous date interval (Excluding weekends and holidays)

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:

yingyinr_0-1627355189292.png

yingyinr_1-1627355189296.png

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:

yingyinr_2-1627355222187.png

 

Detailed steps:

1. Sample data

yingyinr_3-1627355222190.png

yingyinr_4-1627355222191.png

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)

yingyinr_5-1627355268512.png

3. Delete columns

yingyinr_6-1627355268512.png

4. Expand List

yingyinr_7-1627355660896.png

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

yingyinr_8-1627355660898.png

6. Merge Holiday table to current table

yingyinr_9-1627355660902.png

yingyinr_10-1627355660912.png

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"

yingyinr_13-1627355792651.png

8. Filter non-working days

yingyinr_14-1627361690864.png

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