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.
Looking for solution in Power Query to count number of weekend days (Saturdays and Sundays) between two dates.
I currently got below function, but result is incorrect here.
For example:
Initial date is 01.05.2022
Final date is 15.05.2022
Expected result is: 5
(1,7,8,14,15 of May)
Any idea how to approach that request?
(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) ),
WeekendDays = (if DaysBetweenDates < 0 then -1 else 1) * List.Count(WeekDaysList)
in
WeekendDays
Solved! Go to Solution.
Hi @Patryk_PL_92 ,
Please try this custom function:
let parameter = (startdate as text, enddate as text) =>
let
Source = {Number.From(Date.From(startdate))..Number.From(Date.From(enddate))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "List of Dates"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"List of Dates", type date}}),
#"Calculated Day of Week" = Table.TransformColumns(#"Changed Type1",{{"List of Dates", Date.DayOfWeek, Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Calculated Day of Week", each [List of Dates] > 4),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {}, {{"No of weekend days", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
in
parameter
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
Here's code how to calculate the networkdays between two dates:
// fnNETWORKDAYS
let func =
(StartDate, EndDate, optional Holidays as list, optional StartOfWeek as number) =>
let
// optional StartOfWeek, if empty the week will start on Monaday
startOfWeek = if StartOfWeek = null then 1 else StartOfWeek,
// cater for empty dates
StartEmpty = if StartDate = null then #date(1900,1,1) else StartDate,
EndEmpty = if EndDate = null then #date(1900,1,1) else EndDate,
// Providing for logic where EndDate is after StartDate
Start = List.Min({StartEmpty, EndEmpty}),
End = List.Max({StartEmpty, EndEmpty}),
// Switch sign if EndDate is before StartDate
Sign = if EndEmpty < StartEmpty then -1 else 1,
// Get list of dates between Start- and EndDate
ListOfDates = List.Dates(Start, Number.From(End - Start) + 1,#duration(1,0,0,0)),
// if the optional Holidays parameter is used: Keep only those dates in the list that don't occur in the list of Holidays;
// otherwise continue with previous table
DeleteHolidays = if Holidays = null then ListOfDates else List.Difference(ListOfDates, List.Transform(Holidays, Date.From )),
// Select only the first 5 days of the week
// The 1 in the 2nd parameter of Date.DayOfWeek makes sure that Monday will be taken as first day of the week
DeleteWeekends = List.Select(DeleteHolidays, each Date.DayOfWeek(_, startOfWeek) < 5 ),
// Count the number of days (items in the list)
CountDays = List.Count(DeleteWeekends) * Sign
in
CountDays ,
documentation = [
Documentation.Name = " Date.Networkdays.pq ",
Documentation.Description = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in holidays. ",
Documentation.LongDescription = " Returns the number of whole working days between StartDate and EndDate similar to the NETWORKDAYS-function in Excel. Working days exclude weekends and any dates identified in (optional) holidays. ",
Documentation.Category = " Date ",
Documentation.Source = " www.TheBIccountant.com https://wp.me/p6lgsG-2fA . ",
Documentation.Version = " 3.1 Catering for empty dates",
Documentation.Author = " Imke Feldmann ",
Documentation.Examples = {[Description = " ",
Code = " ",
Result = " "]}]
in
Value.ReplaceType(func, Value.ReplaceMetadata(Value.Type(func), documentation))
Using this it should be easy to calculate the non-working days. The code above comes from Date.Networkdays function for Power Query and Power BI – (thebiccountant.com).
All you have to do now is to get the total number of days between the two dates and subtract what the function will return.
Hi daXtreme,
that is a very complex code - for sure it will be good inspiration for some of my projects, thank you for that 🙂
The code is complex but it does not matter at all. All you have to do is copy it and just use the function. And using it is... no more complex than using any other function in PQ, including the bulitins. But the function above gives you much more than you need. One day you might want, for instance, incorporate a holiday calendar. The function above is ready for that without you having to make a single change.
Hi @Patryk_PL_92 ,
Please try this custom function:
let parameter = (startdate as text, enddate as text) =>
let
Source = {Number.From(Date.From(startdate))..Number.From(Date.From(enddate))},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "List of Dates"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"List of Dates", type date}}),
#"Calculated Day of Week" = Table.TransformColumns(#"Changed Type1",{{"List of Dates", Date.DayOfWeek, Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Calculated Day of Week", each [List of Dates] > 4),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {}, {{"No of weekend days", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
in
parameter
Kind regards,
Rohit
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂
Hi Rohit,
this is perfect, exactly what I was looking for.
The only thing I needed to change is:
let parameter = (startdate as text, enddate as text) =>
change "text" to "date" and then it works 🙂
Thank you
Thanks @Patryk_PL_92 ! Glad to hear it worked for you. Would really appreciate it if you could leave me kudos for the effort! 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.