- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you dufoq3, i think this works fine for the porpose!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
07-02-2024 10:18 PM | |||
08-09-2024 11:42 PM | |||
08-06-2024 02:17 AM | |||
07-15-2024 09:49 AM | |||
08-06-2024 06:42 AM |