WorkDays Function in Power Query M

[Anonymous]

I am trying the create a function that replicates the Workdays function typically found in Excel.

I have gotten so far;

//fnWorkDays
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

func

When I invoke the function it works but when I apply it to Columns StartDate and WorkDays in a table it isn't working.

It then throws an extremely long error message.

Anybody know the solution?

[ImkeF · Accepted solution]

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
func

[jthomson]

If 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 · Accepted solution]

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
func

[Anonymous]

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

[Anonymous]

@ImkeF  

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

[ImkeF]

Hi @Anonymous ,

if that works for you, I would rather not spend my time on it and like to leave it as it is.

[Anonymous · Accepted solution]

Thank 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

[Anonymous]

@ImkeF  - 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

[ImkeF]

Hi @Anonymous ,
I don't understand the requirment, unfortunately.
Could you please give an example with sample values (before and desired after)?
Thanks!

[Anonymous]

Hi @ImkeF  , 

Apologies if i was not clear on my requirement . Request :  i just want to retrive the last working day and pass this date as a parameter in another script to fetch data . 

So from this function , all i want is to retreive the last working day . E.g. If today is Monday (31 May 2021) , then my last working day is Last friday ( 28 May 2021) .  

For clarity - all days Mon-Fridays are working day in our firm , which includes all public holidays as well, so data is made available from Monday to Friday in our DWH . 

The above function you had shared is great but i would need to pass a date there , in my case the start date would always be today . i tried to pass the start date as (DateTime.Date(DateTime.LocalNow())) but was unsuccessful .

Hope you can help me here .

[Anonymous]

@ImkeF  please ignore my requirement , i got the solution working now . Again thanks for your time .

[Greg_Deckler]

@ImkeF might be able to help with the Power Query. 

If you just want a solution, I have a DAX version:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362