Forum Discussion

jcamilo1985's avatar
jcamilo1985
Icon for Helper III rankHelper III
3 years ago
Solved

calculate date without holidays (only working days)

Good night After trying for several hours I had no choice but to resort to your help I have the following challenge, I have a table with a date to which I have to add a number of days based on a co...
Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    jcamilo1985 I am afraid I can't comment on this. Probably because my function uses recursive call. I don't know. It's quite easy to reproduce this function using List.Generate. I'll try to do that a bit later, mate.  

  • jcamilo1985's avatar
    jcamilo1985
    3 years ago

    Thank you very much for your commitment and interest in this topic AlienSx .
    After several attempts, I was finally able to find the solution, but all thanks to the base that you gave me.

    Here I share it in case someone else needs it at some point.
    In the same way thanks to the other people who read the thread and were interested.

     

    (start as date, days as number, holidays as list) as date =>
  let
    dates = List.Generate(
      () => [
        date = start,
        count = 1,
        holiday = List.Contains(holidays, start)
          or List.Contains({5, 6}, Date.DayOfWeek(start, Day.Monday))
      ],
      each [count] <= days,
      each [
        date = Date.AddDays([date], 1),
        count = [count] + (if [holiday] then 0 else 1),
        holiday = List.Contains(holidays, Date.AddDays([date], 1))
          or List.Contains({5, 6}, Date.DayOfWeek(Date.AddDays([date], 1), Day.Monday))
      ],
      each [date]
    ),
    result = List.LastN(dates, 1){0}
  in
    result

     

     

     

AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
3 years ago

jcamilo1985 so basically when you "add" 1 business day to March 31st you get the same date? Okay. Just change "exit" condition to result = if days = 1 then start like in the code below.  

let
// start = your date
// days = number of business days to add
// holidays = list with holidays (list of dates), can be empty list {}
    add_business_days = (start as date, days as number, holidays as list) as date => 
        let 
            next = Date.AddDays(start, 1),
            holi = List.Contains(holidays, next) or List.Contains({5, 6}, Date.DayOfWeek(next,Day.Monday)),
            result = if days = 1 then start 
                        else @add_business_days(next, days - (if holi then 0 else 1), holidays)
        in 
            result,
    s = #date(2023, 06, 23),
    d = 15,
    h = {#date(2023, 04, 06), #date(2023, 04, 07), #date(2023, 07, 03)},
    next_b_date = add_business_days(s, d, h)
in
    next_b_date

 This test (June 23rd with July 3rd as holiday and 15 days) gives July 14th.

jcamilo1985's avatar
jcamilo1985
Icon for Helper III rankHelper III
3 years ago

Simply brilliant, the function is returning the expected result, however now I have another unexpected problem.

I am running the code in power query online for a datamart

and I'm getting this error at the time of data load:

 

 

 AlienSx  Are you familiar with this message, what can I do? Thanks again for the feature.

  • AlienSx's avatar
    AlienSx
    Icon for Super User rankSuper User
    3 years ago

    jcamilo1985 I am afraid I can't comment on this. Probably because my function uses recursive call. I don't know. It's quite easy to reproduce this function using List.Generate. I'll try to do that a bit later, mate.  

    • jcamilo1985's avatar
      jcamilo1985
      Icon for Helper III rankHelper III
      3 years ago

      Thank you very much for your commitment and interest in this topic AlienSx .
      After several attempts, I was finally able to find the solution, but all thanks to the base that you gave me.

      Here I share it in case someone else needs it at some point.
      In the same way thanks to the other people who read the thread and were interested.

       

      (start as date, days as number, holidays as list) as date =>
  let
    dates = List.Generate(
      () => [
        date = start,
        count = 1,
        holiday = List.Contains(holidays, start)
          or List.Contains({5, 6}, Date.DayOfWeek(start, Day.Monday))
      ],
      each [count] <= days,
      each [
        date = Date.AddDays([date], 1),
        count = [count] + (if [holiday] then 0 else 1),
        holiday = List.Contains(holidays, Date.AddDays([date], 1))
          or List.Contains({5, 6}, Date.DayOfWeek(Date.AddDays([date], 1), Day.Monday))
      ],
      each [date]
    ),
    result = List.LastN(dates, 1){0}
  in
    result

       

       

       

      • AgataJ's avatar
        AgataJ
        Icon for Helper II rankHelper II
        1 year ago

        Hi jcamilo1985 , thanks for sharing the working solution, this is exactly what I needed today!

    • AlienSx's avatar
      AlienSx
      Icon for Super User rankSuper User
      3 years ago

      jcamilo1985 List.Generate instead of recursion

      let
// start = your date
// days = number of business days to add
// holidays = list with holidays (list of dates), can be empty list {}
    add_business_days = (start as date, days as number, holidays as list) => 
        let 
            g = 
                List.Generate(
                    () => [w = start, d = days, stop = false],
                    (x) => not x[stop],
                    (x) => 
                        let 
                            next = Date.AddDays(x[w], 1), 
                            holi = List.Contains(holidays, next) or List.Contains({5, 6}, Date.DayOfWeek(next,Day.Monday))
                        in [w = next, d = x[d] - (if holi then 0 else 1), stop = d < 1 or (d = 1 and holi)],
                    (x) => x[w]
                )
        in List.Last(g),
        
    s = #date(2023, 06, 23),
    d = 15,
    h = {#date(2023, 04, 06), #date(2023, 04, 07), #date(2023, 07, 03)},
    next_b_date = add_business_days(s, d, h)
in
    next_b_date