Forum Discussion

skv17's avatar
skv17
Helper II
2 years ago
Solved

Skip Weekends when adding date

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 

Need Help
Show and Tell

8 Replies

    • skv17's avatar
      skv17
      Helper II
      2 years ago

      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's avatar
        Ahmedx
        Super User
        2 years ago

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

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    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]))

     

     

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    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}