Couldn't find an equivalent to Excel's WORKDAY(), so I made it myself

[jcountryman]

Couldn't find any posts about functions that can add or subtract workdays from a date, but I'm sure someone will point out a simpler method after I post this. 🙃

Works mostly like WORKDAY() does in Excel:

• If this_move zero, this_date will be returned, wether it's a holiday or not.
• Unlike Excel's WORKDAY function, the second parameter requires a duration.
• If this_move contains any fraction of a day, the result will be moved a whole day, e.g: 
  

fnWorkday(
    #date(2021,08,20), //a Friday
    #duration(0,0,0,1) //one *second* later
)

…returns Monday 2021-08-22. Conversely,

fnWorkday(
    #date(2021,08,22), //a Monday
    #duration(0,0,0,1) //one *second* before
)

…returns Friday 2021-08-20.

• The HOLIDAYS variable should be a list of dates to skip (or just remove that clause entirely)

fnWorkday=(
    this_date as date, 
    this_move as duration 
)=> let
    fnDurationSign=(_)=> Number.Sign( 
        Number.From(_) 
    ),
    ACTION = if this_move>=#duration(0,0,0,0)
        then [ 
            DAYS = #duration(1,0,0,0), 
            MOVE = #duration(-1,0,0,0)
        ]
        else [
            DAYS = #duration(-1,0,0,0), 
            MOVE = #duration(1,0,0,0)
        ],
    next_date = this_date+ACTION[DAYS],
    next_move = this_move+ACTION[MOVE], 
    next_date_is_holiday = List.Contains( 
            List.Buffer(
                HOLIDAYS //**IMPORTANT** A list of your own holidays to skip
            ), next_date
        ) or Date.DayOfWeek(next_date) = Day.Saturday
        or Date.DayOfWeek(next_date) = Day.Sunday,
    sign_changed = fnDurationSign(this_move)<>fnDurationSign(next_move) 
in if this_move = #duration(0,0,0,0) 
    then this_date 
    else if sign_changed and not next_date_is_holiday
        then next_date 
        else if next_date_is_holiday 
            then @fnWorkday(
                next_date, 
                this_move  
            )
            else @fnWorkday( 
                next_date,
                next_move
            ),

[jcountryman · Accepted solution]

@lbendlin And you're making an assumption that I was trying to emulate Excel's WORKDAYS.INTL(). 

Me: adds #ShowAndTell tag

Super User III: "It doesn't work for cases that are out-of-scope."

Me: Never posts here again

[lbendlin]

You're making an assumption that Saturday and Sunday are weekend days. That assumption is not valid for a large number of countries.

It's the weekend! Or is it? - Microsoft Power BI Community