Forum Discussion
New Date without considering Holidays or Weekends
Hi JoaoFidalgo94, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrYwMlfSUTIy0Tc00DcyMDJRitWBixqY6hsaQUVjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Conjunto = _t, #"Data Recep ARM" = _t]), Feriado = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQNzTSNzIwMlHSUXJLLcpMTMlXitUBShjjkjDBJWGKS8IMl4Q5DgljXK4yNsQhYWCoD0RACVNkiVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, Tipo = _t]), Holidays = List.Buffer(List.Transform(Feriado[Data], Date.From)), StepBack = Source, ChangedType = Table.TransformColumnTypes(StepBack,{{"Data Recep ARM", type date}, {"Conjunto", Int64.Type}}), Ad_Plus15WorkingDays = Table.AddColumn(ChangedType, "Plus 15 working days", each [ a = List.Skip(List.Dates([Data Recep ARM], 40, #duration(1,0,0,0))), b = List.Select(a, (x)=> (not List.Contains(Holidays, x)) and Date.DayOfWeek(x, Day.Monday) < 5) ][b]{14}, type date ) in Ad_Plus15WorkingDays
Super User- BeaBF
JoaoFidalgo94 Try with:
let
// Load Main Table
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLQNzTSNzIwMlHSUXJLLcpMTMlXitUBShjjkjDBJWGKS8IMl4Q5DgljXK4yNsQhYWCoD0RACVNkiVgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t, Tipo = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Data", type date}, {"Tipo", type text}}),// Load Holidays Table (Replace with your actual holiday table)
HolidayTable = Table.SelectRows(#"Changed Type", each [Tipo] = "Feriado"),
HolidayDates = List.Buffer(HolidayTable[Data]),// Function to Add Working Days
AddWorkingDays = (startDate as date, workingDays as number) as date =>
let
DayIncrement = List.Numbers(1, workingDays * 2), // Generate numbers (an overestimate to handle skips)
PotentialDates = List.Transform(DayIncrement, each Date.AddDays(startDate, _)),
WorkingDates = List.RemoveMatchingItems(
List.Select(PotentialDates, each Date.DayOfWeek(_, Day.Monday) < 5), // Remove weekends
HolidayDates // Remove holidays
),
Result = List.FirstN(WorkingDates, workingDays){workingDays - 1}
in
Result,// Apply Function to Main Table
MainTable = Table.AddColumn(#"Changed Type", "Date after 15 working days", each
if [Tipo] <> "Feriado" and [Data] <> null then
AddWorkingDays([Data], 15)
else
null,
type date
)
in
MainTableBBF
onjunto Data Recep ARM Date after 15 working days without holidays and weekends 3827
24/10/2024 14/11/2024 3827 05/12/2024 07/01/2024
