Forum Discussion
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
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
SourceDid I answer your question? Mark my post as a solution!
Ricardo
3 Replies
- amitchandak
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
camargos88Community 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
SourceDid I answer your question? Mark my post as a solution!
Ricardo
