New Date without considering Holidays or Weekends

[JoaoFidalgo94]

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

[CSrikanth_21]

Hi @JoaoFidalgo94 

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.

[dufoq3 · Accepted solution]

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

[JoaoFidalgo94]

Thank you dufoq3, i think this works fine for the porpose!

[CSrikanth_21]

Hi @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!

[JoaoFidalgo94]

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? 

[CSrikanth_21]

Hi @JoaoFidalgo94 

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

[BeaBF]

@JoaoFidalgo94 Hi! can you write your expected output?

BBF

[JoaoFidalgo94]

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

[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
MainTable

BBF