NetworkDays function to account for different types of work weeks

[Ry009]

I found this function which was a great help https://community.fabric.microsoft.com/t5/Community-Blog/Date-Networkdays-function-for-Power-Query-a...

In my case, I'm trying to define multiple leave records into one over a period, and ensuring that is essentially a single stretch of leave with no actual break.

This is fine for people who work 5 days (1 FTE)  a week, but those who work under 5 days a week, this where it gets a bit tricky. 

The added layer of complexity is that different staff have different days of the week. I understand in DAX (https://learn.microsoft.com/en-us/dax/networkdays-dax) that you can incorprate a number to account for a different weekend, but I would still need to incorprate a weekend as well as their non days of work during the week (which even then may not be the 17 listed there)

I can pull in a value that can be used as a parameter from the data to calculate which days to omit (as well as the weekend) but I would have no idea how to incorporate this into a NETWORKDAYS function.

Any help?

[Ry009 · Accepted solution]

For anyone with a similar issue (Holidays was not being the problem.... accounting for people with different rosters of work was), these two functions worked well for my reports. From the first function I found, I modified it slightly so I could use it for different purposes (day difference vs counting days etc.) with 'DaysDif_ZeroForNoChange' the parameter to add/subtract from the count if required.

Hope this helps someone else.

(StartDate as date, EndDate as date, DaysDif_ZeroForNoChange as number, optional Holidays as list) =>
let
    // Get list of dates between Start- and EndDate
    ListOfDates = List.Dates(StartDate,Number.From(EndDate-StartDate),#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, Holidays),

    // 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(_,1) < 5 ),

    // Count the number of days (items in the list) + Days Difference depending on use case eg: for leave '1' adds one day, -1 subtracts
    CountDays = List.Count(DeleteWeekends) + DaysDif_ZeroForNoChange
in
    CountDays

(StartDate, EndDate, optional Weekend, optional Holidays)=> 
let
    //if a date is blank, make it equal to 30-Dec-1899
    FromDate = if StartDate = null then #date(1899,12,30) else StartDate,
    ToDate = if EndDate = null then #date(1899,12,30) else EndDate,
    //StartDate should be lesser than or equal to EndDate. If it is more than EndDate, then we will multiply the final result with negative sign. Let's store this sign
    Sign = if ToDate<FromDate then -1 else 1,
    Start_Date = List.Min({FromDate, ToDate}),
    End_Date = List.Max({FromDate, ToDate}),
    //FIX: support no Holidays option. Insert line below <<<<<<<<
    Holidays = if Holidays = null then {} else Holidays,
    //Prepare a list of dates from Start date to End date and remove Holidays from there
    ListOfAllDates = List.Difference(List.Dates(Start_Date,Duration.Days(End_Date-Start_Date)+1,#duration(1,0,0,0)),Holidays),
    //Adjust Weekend Parameter
    SetWeekend = if Weekend ="" or Weekend = null then "1" else Text.From(Weekend),
    //1 to 7 and 11 to 17 are parameters for weekends - https://support.microsoft.com/en-us/office/networkdays-intl-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28
    WeekendParamList = {"1".."7"} & {"11","12","13","14","15","16","17"},
    WeekendLookupList = {"0000011","1010000","1000001","1100000","0110000","0011000","0001100","0000110","0000001","1000000","0100000","0010000","0001000","0000100","0000010"},
    WeekendString = if Text.Length(SetWeekend)=7 then SetWeekend else WeekendLookupList{List.PositionOf(WeekendParamList,SetWeekend)},
    //Let's generate a list which has the position of weekends. Hence for 1000011 will generate, {1,6,7}.
    WeekendList = List.RemoveMatchingItems(List.Transform(List.Positions(Text.ToList(WeekendString)), (i)=>(i+1)*Number.From(Text.ToList(WeekendString){i})),{0}),
    //Let's remove the dates which match the weekend criteria and take the count multiplied by Sign
    Networkdays = Sign * List.Count(List.Select(ListOfAllDates,(i)=>not List.Contains(WeekendList,Date.DayOfWeek(i,1)+1)))
in
    Networkdays

[lbendlin]

NETWORKDAYS has another parameter (holidays) which can accept arbitrary lists.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...