Power BI - Add N WorkDays to a Date

Question

I have a calendar table that has a column storing Flag to Indicate if a calendar date is a BusinessDay (WorkDay). WorkDay_FL = 0 means the Date is either weekend or Holiday. I want to create a calculated column to add 5 working days to a given Date.

So if Date = 12/30/2020, i.e. 30-Dec-2020 then adding 5 WorkDays should give me 1/7/2020 (7-Jan-2021).

31-Dec-2020 = WeekDay
1-Jan-2021 = Holiday
2-Jan-2021 = Weekend
3-Jan-2021 = Weekend
4-Jan-2021 = WeekDay
5-Jan-2021 = WeekDay
6-Jan-2021 = WeekDay
7-Jan-2021 = WeekDay

Either a DAX formula or Power Query Function will work.

Thanks

This would be clearer if you posted exactly what your data looks like. Does the box contain your "Flag" column? Are the items in the box all in the same column? — Hooded 0ne

sergiom sergiom · Accepted Answer · 2021-01-12T09:38:51

The easiest way is to create a calculated table in your calendar table with a progressive number on working days. For instance, assuming that your WorkDay_FL is 1 for working days

WorkDayProg = 
VAR CurDate = 'Date'[Date]
RETURN CALCULATE( SUM('Date'[WorkDay_FL]), 'Date'[Date] <= CurDate, REMOVEFILTERS('Date') )

and the use the WorkDayProg calculated column to compute the future (or past) dates on working days only

FiveWorkingDaysInTheFuture =
VAR CurrentWorkDay =
    MAX( 'Date'[WorkDayProg] )
RETURN
    CALCULATE(
        MIN( 'Date'[Date] ),
        'Date'[WorkDayProg] = CurrentWorkDay + 5,
        REMOVEFILTERS( 'Date' )
    )

we use MIN since the non-working days have WorkDayProg equal to the last working day.

Power BI - Add N WorkDays to a Date

1 Answers