cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Exclude Holiday in existing Networkday code

Hi all

I have a code where I exclude the weekends to get the networkdays. Can someone help me to additional exclude the holidays which I want to set up in an excel file?

This is my existing code

(StartDate as date, EndDate as date) as number =>
let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)
in
CountDays

Thanks a lot!

1 ACCEPTED SOLUTION
Community Champion
``````(StartDate as date, EndDate as date) as number =>
List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
6 REPLIES 6
Community Champion

``````let
ListDates = List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)),
RemoveWeekendsHolidays = List.Select(ListDates, each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _))
CountDays = List.Count(RemoveWeekends)
in
CountDays``````
``````(StartDate as date, EndDate as date) as number =>
List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Helper I

I don't really know how I should put this in my existing code... Could you write it with my existing code

Community Champion
``````(StartDate as date, EndDate as date) as number =>
List.Count(List.Select(List.Dates(StartDate, Number.From(EndDate-StartDate) + 1, #duration(1, 0, 0, 0)), each Date.DayOfWeek(_, Day.Monday) < 5 or not List.Contains(Holidays, _)))``````

 Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! DAX is simple, but NOT EASY!
Helper I

Thanks a lot! Any idea why the data load takes such a long time? is running for 10min and still didn't load the data...And I only have 3 dates to test in the holiday file.

Helper I

Community Support

Hi @jij19,

I'd like to suggest you add buffer functions to these processes to prevent memory leaks and duplicated memory resources spent on these calculations.

Chris Webb's BI Blog: New Options For The Table.Buffer Function In Power Query Chris Webb's BI Blog ...

Table.Buffer - PowerQuery M | Microsoft Docs

List.Buffer - PowerQuery M | Microsoft Docs

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors