Forum Discussion
Calculate due date based on SLA excluding weekends and holidays date conversion error
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
Super UserThe 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.
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