Forum Discussion

b2wise's avatar
b2wise
Icon for Helper III rankHelper III
5 years ago
Solved

Expected Ship Date-Business Day Rules

Hi all,

 

I am creating a calculated table for Amazon orders in order to calculate their "Expected Ship Date". The rule is if an order was created on a business day it has to ship the next business day by 8:00 PM, if it was created on a non business day it has to ship on the second business day after it was created by 8:00 PM.

 

I have a date table and have attempted a formula but it's timing out and not returning the right results. Here are sample tables:

 

Headers

 

DATE_CREATEDIMS_ORDER_NOTO_NUMBERDEPARTMENT
Thursday, July 1, 20211281651SO60127657Amazon
Friday, July 2, 20211282596SO60158377Amazon
Monday, July 5, 20211283155SO60167492Amazon
Sunday, July 4, 20211282826SO60194903Amazon
Tuesday, July 6, 20211285743SO60256662Amazon

 

Dates

DateIsHolidayIsBusinessDayDay Type
Thursday, July 1, 2021FALSETRUEWeekday
Friday, July 2, 2021FALSETRUEWeekday
Saturday, July 3, 2021FALSEFALSEWeekend
Sunday, July 4, 2021TRUEFALSEHoliday
Monday, July 5, 2021TRUEFALSEHoliday
Tuesday, July 6, 2021FALSETRUEWeekday
Wednesday, July 7, 2021FALSETRUEWeekday
Thursday, July 8, 2021FALSETRUEWeekday
Friday, July 9, 2021FALSETRUEWeekday
Saturday, July 10, 2021FALSEFALSEWeekend
Sunday, July 11, 2021FALSEFALSEWeekend
Monday, July 12, 2021FALSETRUEWeekday
Tuesday, July 13, 2021FALSETRUEWeekday
Wednesday, July 14, 2021FALSETRUEWeekday
Thursday, July 15, 2021FALSETRUEWeekday
Friday, July 16, 2021FALSETRUEWeekday
Saturday, July 17, 2021FALSEFALSEWeekend
Sunday, July 18, 2021FALSEFALSEWeekend
Monday, July 19, 2021FALSETRUEWeekday
Tuesday, July 20, 2021FALSETRUEWeekday
Wednesday, July 21, 2021FALSETRUEWeekday
Thursday, July 22, 2021FALSETRUEWeekday
Friday, July 23, 2021FALSETRUEWeekday
Saturday, July 24, 2021FALSEFALSEWeekend
Sunday, July 25, 2021FALSEFALSEWeekend
Monday, July 26, 2021FALSETRUEWeekday
Tuesday, July 27, 2021FALSETRUEWeekday
Wednesday, July 28, 2021FALSETRUEWeekday
Thursday, July 29, 2021FALSETRUEWeekday
Friday, July 30, 2021FALSETRUEWeekday
Saturday, July 31, 2021FALSEFALSEWeekend

 

My attempted formula for what it's worth... I didn't get up to the 8:00 PM part either.

Amazon Expected Ship =
CALCULATETABLE (
ADDCOLUMNS (
SUMMARIZE (
HEADERS,
HEADERS[IMS_ORDER_NO],
HEADERS[TO_NUMBER],
HEADERS[DATE_CREATED]
),
"Expected Ship Date",
CALCULATE (
VAR DCisbusinessday =
CALCULATE ( MAX ( HEADERS[DATE_CREATED] ), Dates[IsBusinessDay] = TRUE () )
VAR NextBusinessDay =
CALCULATE (
MIN ( Dates[Date] ),
FILTER (
ALL ( Dates[Date], Dates[IsBusinessDay] ),
Dates[Date] > MAX ( HEADERS[DATE_CREATED] )
&& Dates[IsBusinessDay] = TRUE ()
)
)
VAR TwoBusinessDays =
CALCULATE (
MIN ( Dates[Date] ),
FILTER (
ALL ( Dates[Date], Dates[IsBusinessDay] ),
Dates[Date] > NextBusinessDay
&& Dates[IsBusinessDay] = TRUE ()
)
)
RETURN
CALCULATE (
SWITCH (
TRUE (),
NOT ( ISBLANK ( DCisbusinessday ) ), NextBusinessDay,
ISBLANK ( DCisbusinessday ), TwoBusinessDays
)
)
)
),
HEADERS[DEPARTMENT] = "Amazon"
)

 

Thanks in advance!

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

    b2wise 

    is this what you want?

    Expected ship date = 
VAR _next=MINX(FILTER('Date','Date'[Date]>Headers[DATE_CREATED] && 'Date'[IsBusinessDay]=True()),'Date'[Date])
VAR _next2=MINX(FILTER('Date','Date'[Date]>_next && 'Date'[IsBusinessDay]=True()),'Date'[Date])
return if(RELATED('Date'[IsBusinessDay])=True(),_next,_next2)

    pls see the attachment below.

4 Replies