How to generate List Of Dates From Start Date & End Date Excluding Weekends And Public Holidays?

[Chanakya_vcr]

Hi Everyone,

I have a table with Emp_Name, Start Date and End Date columns. I am able to calculate the No. Of Days b/w Start Date & End Date excluding weekends using Networkdays function and I also generated the list of dates b/w these Start & End Dates. But I would like to account for holidays & weekends  and exclude those dates as well from the List Of Dates.

Here is an example of my table:-

Here, in the above table, all the dates are being reflected which also includes weekends and public holidays.

So, based on the weekends and holidays, the expected output should look like this:-

List Of Dates

02-May-24

03-May-24

06-May-24

07-May-24

 and No. Of Days Including Weekends and Holidays should be 4

01-May-2024 is International Workers' Day (a Public Holiday)

04-May-24 & 05-May-24 are excluded as they are weekends,

[Syndicate_Admin]

you must create a table of holidays based on your country with a single column (holidays) in Query.

then in DAX you can use the Networkdays function to calculate days between the two dates, taking into account the weekends and holidays you indicate.

NETWORKDAYS function (DAX) - DAX | Microsoft Learn

The formula might look like this:

NETWORKDAYS(start_date, end_date, 1, tabla_festivos)

[Chanakya_vcr]

Hi @Syndicate_Admin,

Thanks for the response. While the logic you provided effectively works for number of days excluding holidays and weekends, I also need to generate list of dates that excludes weekends and holidays. Could you please assist with this additional requirement?

[danextian]

Hi @Chanakya_vcr 

I am assuming you generated the list of dates using Power Query. To exlude weekends, add another column that extract the name of day from the list of dates and filter out Saturday and Sunday. To exclude the holidays, you must have a table of holidays in Power Query. Do a merge with the list of dates on the left and list of holidays on the right using Left anti join.

[Chanakya_vcr]

Hi @danextian,

Thanks you for you suggestion. I have previously implemented the method you described. However, I am looking for a more streamlined approach or shortcut to achieve the requirement directly within Power Bi Desktop. Any additional insights wouldv be greatly appreciated.

[danextian · Accepted solution]

Doing it in the query editor is still within Power BI Desktop or do you mean DAX?

This is  a sample DAX calculated table.

Dates = 
VAR _FromTo =
    DISTINCT ( FromTo )
VAR ListOfDates =
    CALENDAR ( MIN ( FromTo[start] ), MAX ( FromTo[end] ) )
VAR Crossjoined =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN ( _FromTo, ListOfDates ),
            "Name of Day", FORMAT ( [Date], "ddd" )
        ),
        NOT [Name of Day] IN { "Sat", "Sun" }
    )
VAR filtered =
    FILTER ( Crossjoined, [Date] >= [start] && [Date] <= [end] )
RETURN
    FILTER ( filtered, NOT [Date] IN VALUES ( Holidays[Holidays] ) )

While you can certainly do it in DAX, this is not the optimal approach. Some things are better than in M or a combination of both.