cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
jcamilo1985
Helper III
Helper III

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 condition in another column, something like if column A is equal to x value then add 2 days to the date column otherwise add 15 days. It looks as follows.

 

jcamilo1985_0-1687484164546.png

 

The problem comes from the fact that the days that must be added are not calendar days, but rather they must not consider Saturdays, Sundays or holidays.


In this aspect I have a table with a single column that has the dates of all Saturdays, Sundays and holidays that must be removed.

In summary I must take date_income and if request_type is x value then add 15 business days otherwise add 2 business days

I don't know how to perform a function that allows me to calculate a similar function. If someone can help me, I would greatly appreciate it.

2 ACCEPTED SOLUTIONS

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

View solution in original post

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

 

 

 

View solution in original post

11 REPLIES 11
AlienSx
Super User
Super User

Hi, @jcamilo1985 try this function

    // 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 nullable list) as date => 
        let 
            next = Date.AddDays(start, 1),
            holi = List.Contains(holidays, next) or List.Contains({6, 0}, Date.DayOfWeek(next))
        in 
            if days = 0 then start 
            else @add_business_days(next, days - (if holi then 0 else 1), holidays)

First of all thank you very much for coming so quickly to my aid @AlienSx . really thank you

I was evaluating the code and I still have a problem, it basically boils down to the fact that it is adding a day to the departure date, therefore the result is not as expected, since I must count the days from the moment the departure date begins.

Prepare the following image where it will be clearer, for the month of March. I asked that 5 working days be calculated (yellow boxes), that is, starting on March 31, the function should return March 10, in this case it returns March 11.

In the case of June, I asked to return 15 business days, that is, my expected response would be July 14, but it returns July 17.

outdated calendar.jpg

 

I tried to modify the code that you provided me but I was not able to obtain the solution, I leave it here modified and I hope that you can please guide me on how to solve this issue. Thank you very much in advance

    // start = your date
    // days = number of business days to add
    // holidays = list with holidays (list of dates), can be empty list {}
    (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 = 0 then start 
                        else @add_business_days(next, days - (if holi then 0 else 1), holidays)
        in 
            result

 

I have a different algorithm to offer, but need you to answer two questions:

 

  • What country are you dealing with so far as holidays are concerned?
  • Are we to assume that Date of Departure + One Day = Date of Departure?

first of all thank you very much @ronrsnfld  for coming to the solution of the thread

1 - holidays are for Colombia
2 - no, we start counting the days from the departure date.

I annex the table of holidays that I have, the real table is older

 

holydays
1/01/2023
7/01/2023
8/01/2023
9/01/2023
14/01/2023
15/01/2023
21/01/2023
22/01/2023
28/01/2023
29/01/2023
4/02/2023
5/02/2023
11/02/2023
12/02/2023
18/02/2023
19/02/2023
25/02/2023
26/02/2023
4/03/2023
5/03/2023
11/03/2023
12/03/2023
18/03/2023
19/03/2023
20/03/2023
25/03/2023
26/03/2023
1/04/2023
2/04/2023
6/04/2023
7/04/2023
8/04/2023
9/04/2023
15/04/2023
16/04/2023
22/04/2023
23/04/2023
29/04/2023
30/04/2023
1/05/2023
6/05/2023
7/05/2023
13/05/2023
14/05/2023
20/05/2023
21/05/2023
22/05/2023
27/05/2023
28/05/2023
3/06/2023
4/06/2023
10/06/2023
11/06/2023
12/06/2023
17/06/2023
18/06/2023
19/06/2023
24/06/2023
25/06/2023
1/07/2023
2/07/2023
3/07/2023
8/07/2023
9/07/2023
15/07/2023
16/07/2023
20/07/2023
22/07/2023
23/07/2023
29/07/2023
30/07/2023
5/08/2023
6/08/2023
7/08/2023
12/08/2023
13/08/2023
19/08/2023
20/08/2023
21/08/2023
26/08/2023
27/08/2023
2/09/2023
3/09/2023
9/09/2023
10/09/2023
16/09/2023
17/09/2023
23/09/2023
24/09/2023
30/09/2023
1/10/2023
7/10/2023
8/10/2023
14/10/2023
15/10/2023
16/10/2023
21/10/2023
22/10/2023
28/10/2023
29/10/2023
4/11/2023
5/11/2023
6/11/2023
11/11/2023
12/11/2023
13/11/2023
18/11/2023
19/11/2023
25/11/2023
26/11/2023
2/12/2023
3/12/2023
8/12/2023
9/12/2023
10/12/2023
16/12/2023
17/12/2023
23/12/2023
24/12/2023
25/12/2023
30/12/2023
31/12/2023

Thank you.

  • Generate a calender including the possible date range from which holidays and weekend days are removed.
  • To add working days, we merely look for the position in the List of working days that corresponds to then number of working days you wish to add.
  • In your specific case, since the departure date is counted as Day 1, you must subtract 1 from the number of days you are looking for.
  • Note that in the code below, I took your list of holidays and put in in a Query named HolyDay for access. But there is also code commented out that can download the holiday list for many countries from the web. (Using a Table for this will be much faster, however.
  • I don't know if you will run into the same problem you had with the other solution

 

//Note optional Holidays parameter
//You can substitute a self-generated List of holidays, or use the website in the code to download a list
//  If you use the website, be sure to use a recognized country name
//If you are using holidays, you should separate this list and buffer the result for speed issues.
//  as written, it will download this list every time the function executes

(start as datetime, numDays as number, optional Country as nullable text) =>

let 
    adjNumDays = Number.RoundAwayFromZero(Number.Abs(numDays*7/5))*2,
    yrStart = Date.Year(start),
    yrEnd = Date.Year(Date.AddDays(start,adjNumDays*Number.Sign(numDays))),

//Create list of country specific Holidays
    holidays = HolyDays[holydays],

    /*yrs = {List.Min({yrEnd,yrStart})..List.Max({yrEnd,yrStart})},
     if Country = null then {} else    
        List.Accumulate(yrs,{},(state,current) =>
             let 
                Source=Web.Page(Web.Contents("https://www.officeholidays.com/countries/" & Country & "/" & Number.ToText(current))),
                Data0 = Source{0}[Data],
                DateCol = Table.SelectColumns(Data0,"Date"),
                fullDate = List.Transform(DateCol[Date], each Date.FromText(_ & " " & Number.ToText(current)))
            in   
                state & fullDate),*/

//all Working dates
//Generates a list of working dates in either Ascending or Descending order
//  based on the Sign of numDays
//  starting with the first working date after Start
    allWorkDates = 
        List.RemoveNulls(
            List.Transform(
                List.DateTimes(start, adjNumDays, #duration(Number.Sign(numDays),0,0,0)),
                    each if _ = start then _ 
                            else 
                                if Date.DayOfWeek(_)=Day.Saturday 
                                    or Date.DayOfWeek(_)=Day.Sunday 
                                    or List.Contains(holidays, Date.From(_))
                                then null 
                                else _)),
    addWorkDays = if numDays = 0 then start else allWorkDates{Number.Abs(numDays)}
    
in
    addWorkDays

 

 

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

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:

 

 

DependencyCycleDetected.jpg

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

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

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

 

 

 

Papermain
Frequent Visitor

Hello, 

 

I think this should work. Again, as you mentioned, one main table and one table with the holiday and weekend dates, like so:

 

2023-06-23 08_47_03-Book1 - Excel.png

 

Name of the weekend and holiday dates query should be "WeekendHolidays" and column name should be as above "HolidayWeekend" or you have to adjust the m code based on your names

 

M code:

 

let
    Source = Excel.CurrentWorkbook(){[Name="Dates"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date_income", Int64.Type}, {"RequesTType", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let Plus15days = List.RemoveMatchingItems({Number.From([Date_income])..[Date_income]+15}, WeekendHolidays[HolidayWeekend]),
Plus2Days = List.RemoveMatchingItems({Number.From([Date_income])..[Date_income]+2}, WeekendHolidays[HolidayWeekend])

in 

if Text.Upper([RequesTType]) = "X" then Plus15days else Plus2Days),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NewDate", each [Date_income] + (List.Count([Custom])-1)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"NewDate", type date}, {"Date_income", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom"})
in
    #"Removed Columns"

 

 

 

This is the output based on the m code:

2023-06-23 08_49_26-Book1 - Excel.png

 

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Kudoed Authors