Forum Discussion
New Date without considering Holidays or Weekends
Hi everyone!
I need a new column with date after 15 networkdays without considering Holidays that i have that table on my power query. How can I do it? I did it but it doesn't ignores me the days on my "Feriado" table, could you please help?
I leave example:
| Data | Tipo |
20/12/2024 | Feriado |
| 23/12/2024 | Feriado |
| 24/12/2024 | Feriado |
| 25/12/2024 | Feriado |
| 26/12/2024 | Feriado |
| 27/12/2024 | Feriado |
| 30/12/2024 | Feriado |
| 31/12/2024 | Feriado |
| 01/01/2025 | Feriado |
| Conjunto | Data Recep ARM | Date after 15 working days without holidays and weekends |
3827 | 24/10/2024 | |
| 3827 | 05/12/2024 |
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
10 Replies
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_Plus15WorkingDaysThank you dufoq3, i think this works fine for the porpose!
You're welcome.
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
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 SrikanthHi JoaoFidalgo94
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!Sorry CSrikanth_21, First of all, thank you very much for your proposed solution. I've since left this question because I've found another way to deal with the problem I had. However, do you have the query or file where you tried it that you can provide me with?
We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please accept as solution to help others benefit. If not, feel free to reach out.
Feel free to reach out if you need any further assistance.
Thank you.
