Skip Weekends when adding date

[skv17]

I have two column - Start Date and Days required to deliver.

So if Start Date is 13/10/2023 which is on a friday and Days required to deliver is 2,so I need a new column called Arrival cut off date which should give me 17/10/2023 which excludes weekends and excludes counting the Start Date as 1st day. so Monday is 1st day and Tuesday 17th is 2nd day.

and during weekdays just add without exclution - so if start date is 10/10/2023 and Days required to deliver is 2 then Arrival cut off date which should give me 12/10/2023 which excludes the current date.

so I need to create a custom column in Power BI M query, not able to get a correct solution for this, please help 

[Ahmedx · Accepted solution]

Please check attached pbix for more details.

[skv17]

Thank you so much!!!  this works ,

one more question - how to a set another parameter in the same m query , where there is a column delivery days and its values are 

Mon-Fri , Mon-Sat,Mon-Sun,Mon-Thu,  Tue-Sat .  and skip other days based on these parameters,

So Mon-Sat - skip sunday , mon- thu - skip friday, saturday and sunday. 

[Ahmedx]

I didn't quite understand you, show the expected result in Excel

[skv17]

start	end	days required	Delivery days	arrival cut-off date
13/10/2023		2	Mon-Fri	17/10/2023
13/10/2023		2	Mon-Sat	16/10/2023
13/10/2023		2	Mon-Sun	15/10/2023
18/10/2023		2	Tue-Thu	23/10/2023

[skv17]

like the calculations that you have done was excluding weekends , so based on the delivery days column it is Mon-Fri,  so based on what I set a condition I set for the row value which i set in the backet SQL , that is if column value is x then Mon-Fri if column value is y then Mon-Sat

So if Column value has Mon-Fri skip counting sat and sun , if Mon-Sat skip sunday 

[Ahmedx]

your calculation logic is incomprehensible to me

[Ahmedx]

or this

List.Select(
List.Generate(()=>
 [Start Date],(x)=>x<=Date.AddDays([Start Date],[Day]*5),  (x)=> Date.AddDays(x,1)), (x)=>  not (Date.DayOfWeek(x) =5   or Date.DayOfWeek(x) =6 or x=[Start Date])){[Day]-1}

[Ahmedx]

and try this

List.Max( List.Range( List.Select(
List.Generate(()=>
 [Start Date],(x)=>x<=Date.AddDays([Start Date],[Day]*5),  (x)=> Date.AddDays(x,1)), (x)=>  not (Date.DayOfWeek(x) =5   or Date.DayOfWeek(x) =6 or x=[Start Date])),0,[Day]))