Add Working Days after Time

[beerman99x]

Hi,

I am trying to add the next available business day to my order creation column after a cut off time in another column or if the date in the original column is a weekend or holiday in my date table. 

My date table is arranged so that a business day is WD, weekend is WE and holiday is PH. 

[Anonymous · Accepted solution]

Hi, @beerman99x 

Based on your information, I create a sample table:

Then create a calculated column, and try the following DAX expression:

NextBusinessDay = 
VAR OrderDate = [Order Creation Date]
VAR CutoffTime = TIME(17, 0, 0)
VAR OrderTime = [Creation Hour]
VAR IsAfterCutoff = OrderTime >= CutoffTime
VAR NextDay1 = 
    CALCULATE(
        MIN('Date'[Date]),
        FILTER(
            'Date',
            'Date'[Date] > OrderDate &&
            'Date'[DayType] = "WD"
        )
    )
VAR NextBusinessDay = 
    IF(
        IsAfterCutoff,
        NextDay1,
        OrderDate
    )
RETURN
    IF(
        MAX('Date'[DayType]) = "WD" && NOT IsAfterCutoff,
        OrderDate,
        NextBusinessDay
    )

Here is my preview:

How to Get Your Question Answered Quickly

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Idrissshatila]

Hello @beerman99x ,

check this https://learn.microsoft.com/en-us/dax/networkdays-function-dax