Calculate expected ship date with different leadtimes and carriers

[Eckhardt_david]

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.

• Carrier_1 does not ship on holidays.
• Carrier_2 does ship on holidays.

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!

[ThxAlot]

SHPMT.pbix

Render you life easy with correct data model.

[Greg_Deckler · Accepted 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

[Eckhardt_david]

@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)

[Greg_Deckler]

@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.