cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

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

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,

1 ACCEPTED SOLUTION
Super User

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 (
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.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
5 REPLIES 5

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)

New Member

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?

Super User

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.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
New Member

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.

Super User

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 (
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.

Proud to be a Super User!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors