Calculate the working days between two dates excluding weekends and holidays

[v-rzhou-msft]

Scenario: 

In many cases, you may need to calculate the working days between two dates. And this article will show you how to find out the exact number of working days (excludes weekends and holidays)

Table used:

Fact Table:

Holiday Table:

Expected Result:

Solution:

DAX:

We usually needed to build a calendar table with consecutive dates when we met this kind of problem in the past, which helps us filter and count the expected data based on the date table.
However, in the latest version of Power BI, we may not need to create such a consecutive calendar table and can directly call function ‘NETWORKDAYS’ to calculate the working days.

NETWORKDAYS is a new DAX feature mentioned in the Power BI since July 2022 Feature Summary.

Power BI July 2022 Feature Summary

‘NETWORKDAYS’ is used to return the whole number of working days between two dates (inclusive).

Syntax:

NETWORKDAYS (<start date>, <end date> [, <weekend>, <holidays>])

Unlike the function DATEDIFF, you can edit parameters to specify which and how many days are weekend days. You can also designate dates in a list as holidays that are not considered working days.

Dates must be specified by using function DATE or as the result of another expression. If start date and end date are both BLANK, the output value is also BLANK.

If either start date or end date is BLANK, the BLANK start date or end date will be treated as Date (1899, 12, 30).

Sample:

Networkdays_excludes_weekends = NETWORKDAYS ('Fact Table (DAX)'[Start Date],'Fact Table (DAX)'[End Date],1)

Networkdays_excludes_weekends&holidays = NETWORKDAYS ('Fact Table (DAX)'[Start Date],'Fact Table (DAX)'[End Date],1,VALUES(Holidays[Date]))

Result:

PQ:

Currently, there is no similar function like ‘NETWORKDAYS’ in the M syntax that can be used directly. However, we can construct a custom function to achieve a similar goal.

Detailed Steps:

1. Duplicate the table ‘Holidays’ and convert it to a date list

2. New blank queries and paste the following codes in ‘Advanced Editor’ to create the function ' Networkdays_excludes_weekends ' and function ‘Networkdays_excludes_weekends&holidays’

Networkdays_excludes_weekends:

(StartDate as date,EndDate as date) as number=>
let
    DateList=List.Dates(StartDate,Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)),
    //create series of Dates
    RemoveWeekends=List.Select(DateList,each Date.DayOfWeek(_,Day.Monday)<5),
    //remove weekends
    Countdays=List.Count(RemoveWeekends)
    //count days
in
  Countdays

Networkdays_excludes_weekends&holidays’:

(StartDate as date, EndDate as date, HolidayList as list) as number=>
let
    DateList=List.Dates(StartDate,Number.From(EndDate-StartDate)+1,#duration(1,0,0,0)),
    //create series of Dates
    RemoveWeekends=List.Select(DateList,each Date.DayOfWeek(_,Day.Monday)<5),
    //remove weekends
    RemoveHolidays=List.RemoveItems(RemoveWeekends, HolidayList),
    //remove holidays
    Countdays=List.Count(RemoveHolidays)
    //count days
in
  Countdays

List.Dates: Returns a list of date values of the specified size.

Number.From: Returns a value from a given value. Here we use it to get the interval between the start date and the end date, which is convenient for generating a list of dates.

Date.DayOfWeek: Returns a number (from 0 to 6) indicating the day of the week of the provided datetime. Here we use it to determine whether it is a weekday (excluding weekends).

List.Select: Returns a list of values from a list that matches the selection condition. Here we are using to extract all weekdays (excluding weekends) in a given list of dates.

List.RemoveItems: Removes all occurrences of the given values in the list2 from list1. Here we use to remove all values in ‘holiday list’ from the specified list.

List.Count: Returns the number of items in the list.

3. Create two custom columns to call function ' Networkdays_excludes_weekends‘ and  function ‘Networkdays_excludes_weekends&holidays’

Result:

The full applied codes as follow: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc/BCcAwCAXQXTxHiFbbZpbg/mvUlJJ+wcvnKeqcpF2Vu2RR+4NRNDBF82oH2lnN0C4wY/FtvhagwVw23mAD7xys1TJ+Jr38sOLeJ++zEQ8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Start Date" = _t, #"End Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Total days", each Duration.Days([End Date] - [Start Date])+1),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Networingdays_excludes_weekends", each Networkdays_excludes_weekends([Start Date], [End Date])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Networkdays_exclude_weenkends&holiday", each #"Networkdays_excludes_weekends&holidays"([Start Date], [End Date], Holidaylist))
in
    #"Added Custom2"

Hope this article can help you solve similar problems.

Author: Eason Fang

Reviewer: Kerry Wang & Ula Huang

Calculate the working days between two dates excluding weekends and holidays-Eason Fang.pbix