Add 1 Day to Current Date and Skip Weekends and Holidays

[TimmK]

Based on the current date I want to add 1 day, but skip weekends and holidays.

Examples

• Current date = April 19, 2023 (WED) + 1 day => April 20, 2023 (THU)
• Current date = April 19, 2023 (WED) + 1 day; Holiday = April 20, 2023 (THU) => April 21, 2023 (FRI)
• Current date = April 21, 2023 (FRI) + 1 day; Weekend = April 22, 2023 (SAT) and April 23, 2023 (SUN) => April 24, 2023 (MON)
• Current date = April 20, 2023 (THU); Holiday = April 21, 2023 (FRI); Weekend = April 22, 2023 (SAT) and April 23, 2023 (SUN) => April 24, 2023 (MON)

What measure can I use for this?

I have the table Holiday with the columns Date and Holiday. The column Date is the date of the holiday, the column Holiday has value 1 if the date is a holiday.

Additionally, I would like to have two variants of the same. Variant 1 = Add 2 days; Variant 2 = Add 3 days

[FreemanZ]

hi @TimmK 

Supposing 20th and 21st are holiday, try to add a calculated column like:

Column = 
MINX(
    FILTER(
        Dates,
        Dates[IsHoliday]=0
            &&NOT WEEKDAY([Date],2) IN {6,7} 
            &&Dates[Date]>EARLIER(Dates[Date])
    ),
    Dates[Date]
)

it worked like:

[TimmK · Accepted solution]

I adapted it to my table and column naming and added the calculated column, but it did not work. It was loading infinitely. But thanks for the try.

The following measure works for me though:

Measure 1 =
VAR CurrentDate = TODAY()
RETURN
IF (
    CurrentDate > EOMONTH (CurrentDate, 0),
    BLANK (),
    CALCULATE (
        MIN ('Date'[Date]),
        FILTER (
            'Date',
            'Date'[Date] > CurrentDate
            && 'Date'[IsWeekend] = 0
            && 'Date'[IsHoliday] = 0
        )
    )
)