Calculate due date based on SLA excluding weekends and holidays date conversion error

[amby0430]

I am stuck, hoping someone can help me figure out this calculation. I have a custom function that removes weekends and holidays. This works really great to get days between two dates, however I am now looking to just input a start date and add a standard 90 day SLA and would give the DATE as a result. Below is my function, however my result gives me a conversion error in red. Please help. Thank you!

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value #date(2025, 5, 26) to type Number.
Details:
Value=5/26/2025
Type=[Type]

Custom Function

(StartDate as date) as date =>

let

DateList = List.Dates(StartDate, Date.From(Date.AddDays(StartDate,90)), #duration(1, 0, 0, 0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, adbe_holiday_calendar),
Result = Date.From(RemoveHolidays)
in

[Omid_Motamedise]

The error occurs because RemoveHolidays returns a list of dates, but you are trying to convert it directly to a date type, which is causing the type mismatch. To fix this, you need to calculate the final date after adding 90 business days. Instead of converting the list of dates to a single date, you can return the last date from the list as the result. 

[amby0430]

@Omid_Motamedise I am following the logic, however utilizing the code from @m_dekorte is only giving me the date forward 90 calendar days. Thoughts where I may be missing something? adbe_holiday_calendar is my holiday calendar.

= (StartDate as date) as date =>
let
DateList = List.Dates(StartDate, 90, Duration.From(1)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
RemoveHolidays = List.RemoveItems(RemoveWeekends, adbe_holiday_calendar),
Result = List.Last(RemoveHolidays)
in
Result

[m_dekorte · Accepted solution]

Hi @amby0430 

There are two clear issues with your code (assuming adbe_holiday_calendar is a list with date type values).

1. The second argument of List.Dates is expects a count as number and you've provided a date: Date.From(Date.AddDays(StartDate,90))  so I guess 90 will do

2. But Result will throw an error as well, RemoveHolidays returns a list of values and can't be converted to a date, however you can for example extract the last date value from that list.

I hope this is helpful

(StartDate as date) as date =>
let
    DateList = List.Dates(StartDate, 90, Duration.From(1)),
    RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
    RemoveHolidays = List.RemoveItems(RemoveWeekends, adbe_holiday_calendar),
    Result = List.Last(RemoveHolidays)
in
    Result

[amby0430]

I thank you dearly @m_dekorte this worked perfectly. I appreciate that you also helped me understand where I went wrong with the query and didn't just provide me the solve without explaining it. Greatly appreciated!

[Anonymous]

Hi @amby0430,

Thanks @m_dekorte  for Addressing the issue.

Glad that your issue is resolved! 😊 Could you please accept it as a solution? This will help other community members with similar issues find the solution faster. 

Regards,

Vinay Pabbu

[amby0430]

@m_dekorte I have found that the weekends and holidays are not being removed from the calculation. If you could please kindly respond if you have thoughts or a solution I would appreciate it. Thank you.

[m_dekorte]

Hi @amby0430,

The most obvious reason would be if values in your adbe_holiday_calendar have a different data type. To illustrate, what would you expect to be the outcome here?

let
    dateTimes = List.DateTimes( DateTime.From(#date(2025, 3, 1)), 10, Duration.From(1)),
    Dates = List.Dates( #date(2025, 3, 2), 9, Duration.From(1)),
    Diff = List.RemoveItems( dateTimes, Dates)
in
    Diff

Hope that helps!