New Date if Weekend (Convert Weekend date to Next Business day date)

[SupaFlyeSnuka]

What is up y'all!?  I'm at a loss here - and frankly not the best at writing queries in PowerBi.  Please help me with this.

I have a date column with an "OpenDate"

I created a new column with the Weekday numbers -- WeekDay = WEEKDAY('Sheet1'[OpenDate], 1)

No I know that Saturday is 7 and Sunday is 1

My weekdays are 2 - 6

I also added a column that says if its a workday -- WorkDay = NOT WEEKDAY( 'Sheet1'[OpenDate] ) IN { 1,7 }

So, what I want to do is IF the Weekday is 1 or 7, take the OpenDate and add the number of days required to make it a business day, get that new date, and populate it into a new column.

For example, say OpenDate was Saturday, February 29, 2020 (= 7).  The next business day would be Monday, March 2, 2020 ( = 2)

I would want to take that Open Date, add 2 days to show the date as Monday, March 2, 2020.

Alternatively, if there was a way to assign the business day number within the given month, that would help too.  i.e 1st business day of the month, 2nd business day of the month to 21st business day of the month.

Thanks in Advance!

[edhans · Accepted solution]

You can use this formula:

Next Work Day =
VAR WorkdayNumber =
    WEEKDAY(
        'Date'[Date],
        2
    )
VAR Result =
    SWITCH(
        TRUE,
        WorkdayNumber < 6, 'Date'[Date],
        WorkdayNumber = 6, 'Date'[Date] + 2,
        WorkdayNumber = 7, 'Date'[Date] + 1
    )
RETURN
    Result

It just adds 2 days if a saturday, or 1 if a sunday.