Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi evryone,
I have a date table structured like this
[Date]
2017-01-01
2017-01-02
...
I'm trying to generate a calculated column that return the "next work (open) day" relative to my [Date] column
I managed to generate a column that identifies if the date is a WORKDAY or a NOWORKDAY by verifying if its a saturday, a sunday or if the date is a Holiday (by looking in my holiday table (T_FERIES)), but i'm not able to calculate the next open day
let Source = Excel.Workbook(File.Contents("\\DATES.xlsx"), null, true), T_DATES_Table = Source{[Item="T_DATES",Kind="Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes(T_DATES_Table,{{"Date", type date}}), #"MERGE - T_FERIES" = Table.NestedJoin(#"Changed Type",{"Date"},T_FERIES,{"JourCal"},"T_FERIES",JoinKind.LeftOuter), #"EXPAND - Feries" = Table.ExpandTableColumn(#"MERGE - T_FERIES", "T_FERIES", {"JourCal"}, {"Ferie"}), #"Added Conditional Column" = Table.AddColumn(#"EXPAND - Feries", "WORKDAY", each if [Ferie] <> null or Date.DayOfWeek(Date.From([Date])) = 0 or Date.DayOfWeek(Date.From([Date])) = 6 then "NOWORKDAY" else "WORKDAY"), #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "MinWorkDay", each List.Min(Table.SelectRows(#"Added Conditional Column"[Date], [WORKDAY] = "WORKDAY"),type number)) in #"Added Custom"
Here's what my table looks like right now. I'm trying to fill the 4th column with the "Next Work Day"
If anyone could help it would be much appreciated!!
Thanks in advance!
Try adding this new custom column
=let mydate=Date.DayOfWeek(Date.From([Date])) in if mydate=5 then Date.AddDays([Date],3) else if List.AnyTrue({mydate=0,mydate=6})=false then Date.AddDays([Date],1) else null
= Table.AddColumn(#"Added Custom", "Custom", each let mydate=Date.DayOfWeek(Date.From([Date])) in
if mydate=5 then Date.AddDays([Date],3) else if List.AnyTrue({mydate=0,mydate=6})=false then Date.AddDays([Date],1) else null)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |