Forum Discussion

beerman99x's avatar
beerman99x
New Member
2 years ago
Solved

Add Working Days after Time

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. 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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