Forum Discussion
New Date without considering Holidays or Weekends
- 1 year ago
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
Thanks BeaBF for your suggested approaches
In addition to that, I’d like to propose an updated solution that resolves the issue while meeting your requirements:
***************************************************************************
Assign Value =
VAR CurrentID = 'Table'[ID]
VAR CurrentDate = 'Table'[Date]
VAR CurrentFreq = 'Table'[Freq]
VAR ExistingValue =
LOOKUPVALUE(
'Table'[Assign Value],
'Table'[ID], CurrentID,
'Table'[Date], CurrentDate,
'Table'[Freq], CurrentFreq
)
RETURN
IF(
NOT(ISBLANK(ExistingValue)),
ExistingValue,
"AAA" & 'Table'[Index]
)
***************************************************************************
If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.
Thanks,
Cheri Srikanth