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
Resident RockstarHi 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
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!
