Expected Ship Date-Business Day Rules

[b2wise]

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_CREATED	IMS_ORDER_NO	TO_NUMBER	DEPARTMENT
Thursday, July 1, 2021	1281651SO	60127657	Amazon
Friday, July 2, 2021	1282596SO	60158377	Amazon
Monday, July 5, 2021	1283155SO	60167492	Amazon
Sunday, July 4, 2021	1282826SO	60194903	Amazon
Tuesday, July 6, 2021	1285743SO	60256662	Amazon

Dates

Date	IsHoliday	IsBusinessDay	Day Type
Thursday, July 1, 2021	FALSE	TRUE	Weekday
Friday, July 2, 2021	FALSE	TRUE	Weekday
Saturday, July 3, 2021	FALSE	FALSE	Weekend
Sunday, July 4, 2021	TRUE	FALSE	Holiday
Monday, July 5, 2021	TRUE	FALSE	Holiday
Tuesday, July 6, 2021	FALSE	TRUE	Weekday
Wednesday, July 7, 2021	FALSE	TRUE	Weekday
Thursday, July 8, 2021	FALSE	TRUE	Weekday
Friday, July 9, 2021	FALSE	TRUE	Weekday
Saturday, July 10, 2021	FALSE	FALSE	Weekend
Sunday, July 11, 2021	FALSE	FALSE	Weekend
Monday, July 12, 2021	FALSE	TRUE	Weekday
Tuesday, July 13, 2021	FALSE	TRUE	Weekday
Wednesday, July 14, 2021	FALSE	TRUE	Weekday
Thursday, July 15, 2021	FALSE	TRUE	Weekday
Friday, July 16, 2021	FALSE	TRUE	Weekday
Saturday, July 17, 2021	FALSE	FALSE	Weekend
Sunday, July 18, 2021	FALSE	FALSE	Weekend
Monday, July 19, 2021	FALSE	TRUE	Weekday
Tuesday, July 20, 2021	FALSE	TRUE	Weekday
Wednesday, July 21, 2021	FALSE	TRUE	Weekday
Thursday, July 22, 2021	FALSE	TRUE	Weekday
Friday, July 23, 2021	FALSE	TRUE	Weekday
Saturday, July 24, 2021	FALSE	FALSE	Weekend
Sunday, July 25, 2021	FALSE	FALSE	Weekend
Monday, July 26, 2021	FALSE	TRUE	Weekday
Tuesday, July 27, 2021	FALSE	TRUE	Weekday
Wednesday, July 28, 2021	FALSE	TRUE	Weekday
Thursday, July 29, 2021	FALSE	TRUE	Weekday
Friday, July 30, 2021	FALSE	TRUE	Weekday
Saturday, July 31, 2021	FALSE	FALSE	Weekend

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!

[ryan_mayu · Accepted solution]

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

[amitchandak]

@b2wise , refer my blog how add subract business days

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[b2wise]

@ryan_mayu  Thank you!!! This works great! I just switched related to lookupvalue for my calculated table. I also added +.833333333 to both variables to get it to 8:00 PM.

@amitchandak I will check out your blog thanks!

[ryan_mayu]

you are welcome