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

[Anonymous]

Incredibly helpful, thanks!

[Rickmaurinus]

Helpful article RicoZhou, 

Your approach looks familiar. 

1. Taking a list of Dates

2. Removing the weekends
3. Count the number of reocrds. 

I've done something similar, also trying to support negative increments, where the comparison date is in the past. 

And sometimes it can be useful to include holidays too. I think you'll enjoy reading this article, https://gorilla.bi/power-query/working-days-between-dates/

I'll drop the code right here: 

let func = 
  (StartDate, EndDate, optional Holidays as list ) =>
let
    // When a date is null value, start calculating from 1 Jan 1900
    DateStart =         StartDate ?? #date( 1900, 1, 1 ), 
    DateEnd =           EndDate   ?? #date( 1900, 1, 1 ),

    // Find the number of days between Start and End Date
    DaysBetween =    Duration.Days( DateEnd - DateStart  ) ,

    // The correct date series requires the number of days FROM start to end,
    //  not the number of days BETWEEN start and end.
    NumberOfDays = DaysBetween + (if DateEnd < DateStart then -1 else + 1 ),

    // Direction indicates date series direction (positive or negative)
    Direction  =        Number.Sign( NumberOfDays ),

    Result =
      List.Count(                     
        List.Difference(              
          List.Select(                 
            List.Dates(
              DateStart, 
              Number.Abs( NumberOfDays ), 
              #duration( Direction, 0, 0, 0) // positive or negative steps
            ), 
            each Date.DayOfWeek( _, Day.Monday ) + 1 <= 5 // excl weekends
          ), 
          Holidays ?? {}  // exclude holidays. If omitted, use empty list
        )  
      ) 
      * Direction       

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[cjbaguley]

This was amazingly helpful for a solution for removing leave from an employees productive hours measure I'm working on. 

Semi related, how would you amend the custom function method to account for date and time. I have a measure calculating time from a request to delivery, but it would be awesomne to be able to remove weekends from that measure as well. As it is specific to the created date/time and delivery date/time, I need that granular level.

I did try amending the (StartDate as date,EndDate as date) to (StartDate as datetime,EndDate as datetime) which looked like it was going to work, but it could not convert the output, so I'm missing something.

[Snax]

@v-rzhou-msft  thanks for the article.

May I ask how we can deal with those workday on Saturday & Sunday?
eg:

for wk 1,  Sat, Sun both will not be workday

for wk 2, Sat will be workday,  Sun doesn't need to work

for wk 3, back to normal, both Sat,Sun will not work

for wk 4, Sun will be workday, Sat will be not work

in weekend para, we can only input 1-7 or 11-17, which limits the type of weekend.

thanks.