Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello everybody,
I have the following issue:
I have a date from which I have to subtract the shipping time (in working days) to determine the planned shipping day.
In this example, the calculated shipping date should be the 14/06/2024, because the weekend shouldn't count.
Further restriction: the shipping time (working days) is variable - so in this case, it is 5 working days, but in another it could be e.g. 12 or 2.
Has anyone an idea of a workaround solution? Or is there a better way via DAX?
Thanks in advance!
Solved! Go to Solution.
@Anett_R this function should solve your problem in PQ
wd = (delivery_date, days) =>
if days = 0
then delivery_date
else @wd(
Date.AddDays(delivery_date, -1),
if List.Contains({0, 6}, Date.DayOfWeek(delivery_date, Day.Monday))
then days
else days - 1
)
Hi @Anett_R, different solution without recursive function:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLUNzDTNzIwMlHSUTJVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, #"shipping time (working days)" = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"date", type date}, {"shipping time (working days)", Int64.Type}}),
Ad_ShippingDate = Table.AddColumn(ChangedType, "shipping date", each
[ a = [#"shipping time (working days)"],
b = List.Dates(Date.AddDays([date], -(a + Number.RoundUp(a/7)*2)), 3, #duration(1,0,0,0)),
c = List.Select(b, (x)=> not List.Contains({5,6}, Date.DayOfWeek(x, Day.Monday))){0}?
][c], type date)
in
Ad_ShippingDate
@Anett_R this function should solve your problem in PQ
wd = (delivery_date, days) =>
if days = 0
then delivery_date
else @wd(
Date.AddDays(delivery_date, -1),
if List.Contains({0, 6}, Date.DayOfWeek(delivery_date, Day.Monday))
then days
else days - 1
)