Add number of working days to a date - power query included

[kasiaw29]

Hi all,

I have a tiny problem that I am most likely overthinking. 

I have a requirement to add a number to a date in working days. So far I got it. I had to merge some queries and I have used Date.AddDays query to get my 'Result'.

Below table is a sample result from my data. It looks great and does exactly what is asked of it.

I now need to be able to omit Saturdays and Sundays if my result happens to be a saturday or sunday: 

Original Date	Days to add	Result	Working day?	Ideal Result
29/06/2020	5	04/07/2020	No, it's a Saturday	06/07/2020 (Monday)

I have created a date dimension table that simply works out week numbers and then determines if a date is a working one or not ( 1,0)

 

Has anyone completed anything like this before? I nudge in the right direction would be much appreciated! 

Thanks! 

[Fowmy · Accepted solution]

@kasiaw29 

This will be your new column to show the expected Result date:

=let D = Date.AddDays([Original Date],[Days To Add]) in 

if Date.DayOfWeek(D,Day.Saturday)=0 then Date.AddDays(D,2)

else if

Date.DayOfWeek(D,Day.Saturday)=1 then Date.AddDays(D,1)

else

D

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS ☺

YouTube | LinkedIn

[kasiaw29]

Hey @Fowmy ! 

I ended up doing pretty much what you have suggested and it works a treat! 

Thanks a lot for input!

[Fowmy]

@kasiaw29 

Glad it worked for you!

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube | LinkedIn

[amitchandak]

@kasiaw29 , not completely clear.  In case you want to add or subtract working days

refer : https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[kasiaw29]

Thanks @amitchandak will look through this solution and update shrotly.