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 everyone,
I would like to calculate a Shipping Date (Deadline) for WebOrders adding the Shipping condition and skipping not working days like Weekend and Public Holidays.
Order Created Date | Shipping Condition | Shipping Date |
23/12/2022 | 48hrs |
In the Example above the order has been create on 23/12/2022 which is Friday so in this case I can consider 24hrs for the 23rd, then I will have to skip 24/12-25/12-26/12-/27/12 because them are public holidays so the actual Shipping date should be 28/12 EOD.
Any help please?
Solved! Go to Solution.
hi @Uspace87
Not sure if i fully get your, you may try to add a column like this:
EOD - NextWorkingDay =
VAR _date = [OrderDate]
RETURN
MINX(
FILTER(
data,
data[OrderDate]> _date
&& NOT WEEKDAY(data[OrderDate], 2) IN {6, 7}
&& NOT [OrderDate] IN VALUES(Holiday[Holidays])
),
data[OrderDate]
)
tried and it worked like this:
the holiday table:
hi @Uspace87
Not sure if i fully get your, you may try to add a column like this:
EOD - NextWorkingDay =
VAR _date = [OrderDate]
RETURN
MINX(
FILTER(
data,
data[OrderDate]> _date
&& NOT WEEKDAY(data[OrderDate], 2) IN {6, 7}
&& NOT [OrderDate] IN VALUES(Holiday[Holidays])
),
data[OrderDate]
)
tried and it worked like this:
the holiday table:
That is brilliant thank you, I had to add some other logic but that formula is perfect! Thank you very much!
Hi @Uspace87,
This is the DAX function you require: https://learn.microsoft.com/en-us/dax/networkdays-dax
Hope it helps.
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |