Forum Discussion
WorkDays Function in Power Query M
Hi Anonymous
that looks a bit buggy, indeed.
However, you formula works if you avoid using the same name for a step than for a variable like so:
let func = (StartDate as date, WorkDays as number) => let WorkDays2 = (WorkDays*2)+7, startDate = if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,2) else if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,1) else StartDate, ListOfDates = List.Dates(startDate, WorkDays2,#duration(1,0,0,0)), DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_,1) < 5 ), WorkDate = List.Range(DeleteWeekends,WorkDays,1), Result = WorkDate{0} in Result in funcThank you for solving it, I tested it and noticed it doesn't work with negative values,
so I went away and updated with the following,
its a bit messy but it works
Hopefully someone who needs it can quickly use it.
//fnWorkDays let func = (StartDate as date, WorkDays as number) => let WorkDays2 = if WorkDays<0 then (WorkDays*2)-7 else (WorkDays*2)+7, StartDate2 = if WorkDays<0 then if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,2) else if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,1) else StartDate else if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,-1) else if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,-2) else StartDate, ListofDates = if WorkDays<0 then List.Dates(Date.AddDays(StartDate2,WorkDays2), -1*WorkDays2+1,#duration(1,0,0,0)) else List.Dates(StartDate2, WorkDays2,#duration(1,0,0,0)), DeleteWeekends = List.Select(ListofDates, each Date.DayOfWeek(_) < 5 ), StartDateRange = if WorkDays<0 then List.PositionOf(DeleteWeekends,StartDate2) else 0, WorkDateRange = if WorkDays<0 then StartDateRange+WorkDays else WorkDays, WorkDate = List.Range(DeleteWeekends,WorkDateRange,1), Result = if WorkDays =0 then StartDate else WorkDate{0} in Result in func
Solution SageIf you can make your start date a Monday every time correctly as you're trying to do, then rather than using lists, abuse modular arithmetic
- derive the number of days between your start and end dates regardless of whether it's a weekend or not
- do (Number.RoundDown (thatnumberofdays/7))*5 to get a count of five for every completed week
- do Number.Mod(thatnumberofdays,7) to get the number of days left, using an if statement to change a 6 to 5
- add the results of step 2&3 together
- ImkeF
Community Champion
Hi Anonymous
that looks a bit buggy, indeed.
However, you formula works if you avoid using the same name for a step than for a variable like so:
let func = (StartDate as date, WorkDays as number) => let WorkDays2 = (WorkDays*2)+7, startDate = if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,2) else if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,1) else StartDate, ListOfDates = List.Dates(startDate, WorkDays2,#duration(1,0,0,0)), DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_,1) < 5 ), WorkDate = List.Range(DeleteWeekends,WorkDays,1), Result = WorkDate{0} in Result in funcThank you for solving it, I tested it and noticed it doesn't work with negative values,
so I went away and updated with the following,
its a bit messy but it works
Hopefully someone who needs it can quickly use it.
//fnWorkDays let func = (StartDate as date, WorkDays as number) => let WorkDays2 = if WorkDays<0 then (WorkDays*2)-7 else (WorkDays*2)+7, StartDate2 = if WorkDays<0 then if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,2) else if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,1) else StartDate else if Date.DayOfWeek(StartDate)=5 then Date.AddDays(StartDate,-1) else if Date.DayOfWeek(StartDate)=6 then Date.AddDays(StartDate,-2) else StartDate, ListofDates = if WorkDays<0 then List.Dates(Date.AddDays(StartDate2,WorkDays2), -1*WorkDays2+1,#duration(1,0,0,0)) else List.Dates(StartDate2, WorkDays2,#duration(1,0,0,0)), DeleteWeekends = List.Select(ListofDates, each Date.DayOfWeek(_) < 5 ), StartDateRange = if WorkDays<0 then List.PositionOf(DeleteWeekends,StartDate2) else 0, WorkDateRange = if WorkDays<0 then StartDateRange+WorkDays else WorkDays, WorkDate = List.Range(DeleteWeekends,WorkDateRange,1), Result = if WorkDays =0 then StartDate else WorkDate{0} in Result in funcImkeF - i have a requriement to extract data from DWH only on weekdays , so i was thinking of using this function to pass the date , for e.g. for Monday run date , my run_date should be last Friday date . The above function works fine , as long as you pass the start date .
but in my case , my start date would always be today to get the last business work date , could you please help here . Thanks in advance
hi ImkeF
Please can you help us to get this function working also when the values are negative.
I actually need the same that Richard but only substracting not adding.
Or is there other work around.
Thank you
m
something like this or is there an easier way? Thank you
let func = (StartDate as date, WorkDays as number) => let WorkDays2 = (Number.Abs(WorkDays)*2)+7, negative = if Date.DayOfWeek(StartDate,Day.Monday)=5 then Date.AddDays(StartDate,-1) else if Date.DayOfWeek(StartDate,Day.Monday)=6 then Date.AddDays(StartDate,-2) else StartDate, positive = if Date.DayOfWeek(StartDate,Day.Monday)=5 then Date.AddDays(StartDate,2) else if Date.DayOfWeek(StartDate,Day.Monday)=6 then Date.AddDays(StartDate,1) else StartDate, startDate = if WorkDays <0 then negative else positive, ListOfDates = if WorkDays <0 then List.Dates(startDate, WorkDays2,#duration(-1,0,0,0)) else List.Dates(startDate, WorkDays2,#duration(1,0,0,0)), DeleteWeekends = List.Select(ListOfDates, each Date.DayOfWeek(_,1) < 5 ), WorkDate = List.Range(DeleteWeekends,Number.Abs(WorkDays),1), Result = WorkDate{0} in Result in func