cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Help to exclude Holidays

Hi, everyone

I have the following Query to get the days between two dates excluding weekends, so now I need exclude Holidays too, anyone have a solution for it? I have Holidays from other table

Query:

let
Source = (InitialDate as date, FinalDate as date ) as number =>
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate)
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WorkingDays
in
Source

Regards

1 ACCEPTED SOLUTION
Community Champion

Hi @Anonymous ,

Try this one:

let
Source = (InitialDate as date, FinalDate as date ) as number =>
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList),
Holidays = List.Select(Holiday[Date], each Date.From(_) >= InitialDate and Date.From(_) <= FinalDate),
Result = WorkingDays - List.Count(Holidays)
in
Result
in
Source

Ricardo

Proud to be a Super User!

3 REPLIES 3
Community Champion

Hi @Anonymous ,

Try this one:

let
Source = (InitialDate as date, FinalDate as date ) as number =>
let
DaysBetweenDates = Duration.Days(FinalDate-InitialDate),
DaysList = List.Dates(List.Min({InitialDate,FinalDate}),Number.Abs(DaysBetweenDates)+1, Duration.From(1)),
WeekDaysList = List.Select(DaysList, each (Date.DayOfWeek(_, Day.Monday) < 5) ),
WorkingDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList),
Holidays = List.Select(Holiday[Date], each Date.From(_) >= InitialDate and Date.From(_) <= FinalDate),
Result = WorkingDays - List.Count(Holidays)
in
Result
in
Source

Ricardo

Proud to be a Super User!

Anonymous
Not applicable

Thanks so much!! you saved me

I tried this method and it works 😄

Regards

Super User

@Anonymous , refer to this file as to how I have used the calendar for getting working days. Check for working days on Page2

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### 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