Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
b2wise
Helper III
Helper III

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!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

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

1.PNG

pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

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

1.PNG

pls see the attachment below.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

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

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

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.