Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I working on a report and need to calculate the expected ship date of an order by adding the given Leadtime_days to the Order_Add_Date. In doing so I want to exclude weekends and holidays.
I've created a table via <Enter Data> in PowerQuery Editor which lists all holidays. The code for this table is:
= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XZRbjgMhDATvku+VBmPM4yxR7n+NnQw2UPksGbvbgPr9fsklcuWU8+vzdxOxXql8SWcxEa9kB3VgHsT7cDsO25X6OWmK+lm7JEPmKZagArJNA5QTcEkWSBauXeigwIGtVcrGsGBc28JRZWc7OsOR0YPRg8FDxbtUWHAqwHsOMDxUilaKVoi2tZturD53kjTgkmm47UbR3fps07FNp/2J5hfaqdJ5oR2inat1rDZm0R0MOBjr1x+Yf6qhOaA5qDlOTU2z6KKOPtZJE/DroR7ooqtVjrlCDA9b5inK+ivlQBeVZfBEtyC4+ugMR0IPQg8CDxnfSlfk2Kb4VnE2VDMWZ3YFdt/NUyUGa8jUoxhzGU+KeAr6QQ9FRSIFdWCkQ2CoMqEUCaWMJEUkqa3ddGN4mKSgSAdlBq1OOceG5o2ffw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t])
The Calculated_Ship_Date in below table is the desired result.
In this example sample I listed 4 orders that were added on 11/08/2023 and are having different leadtimes and carriers.
3 Orders will be shipped with Carrier_1 and 1 order will be shipped with Carrier_2.
12/08/2023 & 13/08/2023 are weekend days so should be skipped in the calculation for both Carriers.
15/08/2023 is a holiday an should only be skipped for Carrier_1 but is a valid result for Carrier_2
Order | Carrier | Order_Add_Date | Leadtime_days | Calculated_Ship_Date |
Order_1 | Carrier_1 | 11/08/2023 | 1 | 14/08/2023 |
Order_2 | Carrier_1 | 11/08/2023 | 2 | 16/08/2023 |
Order_3 | Carrier_1 | 11/08/2023 | 3 | 17/08/2023 |
Order_4 | Carrier_2 | 11/08/2023 | 2 | 15/08/2023 |
11/08/2023: Friday (Order_Add_Date)
12/08/2023: Saturday (weekend) skipped because weekend for all orders for Carrier_1 and Carrier_2
13/08/2023: Sunday (weekend) skipped because weekend for all orders for Carrier_1 and Carrier_2
14/08/2023: Monday Result Order_1
15/08/2023: Tuesday (Holiday) skipped because holiday for all Carrier_1 orders . Result for Order_4 with Carrier_2
16/08/2023: Wednesday Result Order_2
17/08/2023: Thursday Result Order_3
What would be the best approach to do this?
Many thanks for the help!
Solved! Go to Solution.
@Eckhardt_david Note sure how to do it in Power Query but in DAX try this as a calculated column. PBIX is attached below signature.
Calculated_Ship_Date =
VAR __OrderAddDate = [Order_Add_Date]
VAR __LeadTimeDays = [Leadtime_days]
VAR __Calendar =
ADDCOLUMNS(
CALENDAR( __OrderAddDate + 1, __OrderAddDate + 1 + __LeadTimeDays * 3),
"__IsWeekend", IF( WEEKDAY([Date],2)>5 , 1, 0),
"__IsHoliday", IF( [Date] IN 'Holidays', 1, 0)
)
VAR __TableCarrier1 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [__IsHoliday] = 0 && [Date] <= EARLIER([Date])))
)
VAR __TableCarrier2 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [Date] <= EARLIER([Date])))
)
VAR __Result =
SWITCH( [Carrier],
"Carrier_1", MAXX(FILTER( __TableCarrier1, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 && [__IsHoliday] = 0),[Date]),
"Carrier_2", MAXX(FILTER( __TableCarrier2, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 ),[Date]),
DATE(1900,1,1)
)
RETURN
__Result
Or as a measure:
Calculated_Ship_Date =
VAR __OrderAddDate = MAX([Order_Add_Date])
VAR __LeadTimeDays = MAX([Leadtime_days])
VAR __Calendar =
ADDCOLUMNS(
CALENDAR( __OrderAddDate + 1, __OrderAddDate + 1 + __LeadTimeDays * 3),
"__IsWeekend", IF( WEEKDAY([Date],2)>5 , 1, 0),
"__IsHoliday", IF( [Date] IN 'Holidays', 1, 0)
)
VAR __TableCarrier1 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [__IsHoliday] = 0 && [Date] <= EARLIER([Date])))
)
VAR __TableCarrier2 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [Date] <= EARLIER([Date])))
)
VAR __Result =
SWITCH( [Carrier],
"Carrier_1", MAXX(FILTER( __TableCarrier1, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 && [__IsHoliday] = 0),[Date]),
"Carrier_2", MAXX(FILTER( __TableCarrier2, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 ),[Date]),
DATE(1900,1,1)
)
RETURN
__Result
Render you life easy with correct data model.
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
@Eckhardt_david Note sure how to do it in Power Query but in DAX try this as a calculated column. PBIX is attached below signature.
Calculated_Ship_Date =
VAR __OrderAddDate = [Order_Add_Date]
VAR __LeadTimeDays = [Leadtime_days]
VAR __Calendar =
ADDCOLUMNS(
CALENDAR( __OrderAddDate + 1, __OrderAddDate + 1 + __LeadTimeDays * 3),
"__IsWeekend", IF( WEEKDAY([Date],2)>5 , 1, 0),
"__IsHoliday", IF( [Date] IN 'Holidays', 1, 0)
)
VAR __TableCarrier1 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [__IsHoliday] = 0 && [Date] <= EARLIER([Date])))
)
VAR __TableCarrier2 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [Date] <= EARLIER([Date])))
)
VAR __Result =
SWITCH( [Carrier],
"Carrier_1", MAXX(FILTER( __TableCarrier1, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 && [__IsHoliday] = 0),[Date]),
"Carrier_2", MAXX(FILTER( __TableCarrier2, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 ),[Date]),
DATE(1900,1,1)
)
RETURN
__Result
Or as a measure:
Calculated_Ship_Date =
VAR __OrderAddDate = MAX([Order_Add_Date])
VAR __LeadTimeDays = MAX([Leadtime_days])
VAR __Calendar =
ADDCOLUMNS(
CALENDAR( __OrderAddDate + 1, __OrderAddDate + 1 + __LeadTimeDays * 3),
"__IsWeekend", IF( WEEKDAY([Date],2)>5 , 1, 0),
"__IsHoliday", IF( [Date] IN 'Holidays', 1, 0)
)
VAR __TableCarrier1 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [__IsHoliday] = 0 && [Date] <= EARLIER([Date])))
)
VAR __TableCarrier2 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [Date] <= EARLIER([Date])))
)
VAR __Result =
SWITCH( [Carrier],
"Carrier_1", MAXX(FILTER( __TableCarrier1, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 && [__IsHoliday] = 0),[Date]),
"Carrier_2", MAXX(FILTER( __TableCarrier2, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 ),[Date]),
DATE(1900,1,1)
)
RETURN
__Result
@Greg_Deckler Many thanks for your reply and the attached .pbix. The solution works perfectly.
One condition I forgot to mention is that the lead time can also be 0 days. In that case the ship date is equal to the add date. Currently the measure is resulting blank when the lead time is 0 days.
To get the add date I've nested the ISBLANK function in RETURN like below. This seems to be working fine but I was wondering if there's a more solid way to incorporate this condition in the measure?
Calculated_Ship_Date_ =
VAR __OrderAddDate = [Add Date]
VAR __LeadTimeDays = [Shipping Lead Time]
VAR __Calendar =
ADDCOLUMNS(
CALENDAR( __OrderAddDate + 1, __OrderAddDate + 1 + __LeadTimeDays * 3),
"__IsWeekend", IF( WEEKDAY([Date],2)>5 , 1, 0),
"__IsHoliday", IF( [Date] IN 'Holidays_BE', 1, 0)
)
VAR __TableCarrier1 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [__IsHoliday] = 0 && [Date] <= EARLIER([Date])))
)
VAR __TableCarrier2 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [Date] <= EARLIER([Date])))
)
VAR __Result =
SWITCH( [Carrier],
"Carrier_1", MAXX(FILTER( __TableCarrier1, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 && [__IsHoliday] = 0),[Date]),
"Carrier_2", MAXX(FILTER( __TableCarrier2, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 ),[Date]),
DATE(1900,1,1)
)
RETURN
IF(ISBLANK(__Result),[Add Date],__Result)
@Eckhardt_david That's a pretty solid approach. You should also be able to do it like this:
Calculated_Ship_Date =
VAR __OrderAddDate = [Order_Add_Date]
VAR __LeadTimeDays = [Leadtime_days]
VAR __Calendar =
ADDCOLUMNS(
CALENDAR( __OrderAddDate, __OrderAddDate + __LeadTimeDays * 3),
"__IsWeekend", IF( WEEKDAY([Date],2)>5 , 1, 0),
"__IsHoliday", IF( [Date] IN 'Holidays', 1, 0)
)
VAR __TableCarrier1 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [__IsHoliday] = 0 && [Date] < EARLIER([Date]))) + 0
)
VAR __TableCarrier2 =
ADDCOLUMNS(
__Calendar,
"__Count", COUNTROWS(FILTER(__Calendar, [__IsWeekend] = 0 && [Date] < EARLIER([Date]))) + 0
)
VAR __Result =
SWITCH( [Carrier],
"Carrier_1", MAXX(FILTER( __TableCarrier1, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 && [__IsHoliday] = 0),[Date]),
"Carrier_2", MAXX(FILTER( __TableCarrier2, [__Count] = __LeadTimeDays && [__IsWeekend] = 0 ),[Date]),
DATE(1900,1,1)
)
RETURN
__Result
Basically, took out the +1 for the __Calendar table and switched the <= to < when calculating the count and added +0 to make sure that it returns 0 instead of BLANK to avoid any potential issues.