I'm very new to using functions within my queries and am running into an interesting dilema. I want to "simply" have a step in my query that takes a date column (or even a defined date) and subtracts 1 business day. I found the following M code somewhere else but it is only useful for adding business days. I thought I could figure out how to modify the code to allow for negative values but I keep running into roadblocks. I have a feeling that I need to somehow change step 1 to create a list of backdated dates, but I can't figure out what needs changed to make that happen. Anyways, I've spent hours on this and figured maybe one of you can take a look at it and say "oh yeah, do this".
let
fnPreviousBusinessDay = (startDate, days) =>
let
Step1 = List.Dates(Date.AddDays(startDate, 1), days + Number.RoundUp(days/7*3+4,0), #duration(1,0,0,0)), //provision list of added days with 3 more days per each added week, starting from startDate + 1 day
Step2 = List.Select(Step1, (item) => Date.DayOfWeek(item, Day.Monday) < 5), // select only workdays
Step3 = List.FirstN(Step2, days), //select required number of workdays
Output = if days <= 0 then startDate else List.Last(Step3)
in
Output
in
fnPreviousBusinessDay
let
fnPreviousBusinessDay = (startDate, days) =>
let
Step1 = List.Dates(Date.AddDays(startDate, 1), days + Number.RoundUp(days/7*3+4,0), #duration(1,0,0,0)), //provision list of added days with 3 more days per each added week, starting from startDate + 1 day
Step2 = List.Select(Step1, (item) => Date.DayOfWeek(item, Day.Monday) < 5), // select only workdays
Step3 = List.FirstN(Step2, days), //select required number of workdays
Output = if days <= 0 then startDate else List.Last(Step3)
in
Output
in
fnPreviousBusinessDay
