Converting a simple Formula to Work in Power BI

[PaulMac]

Hi

We have a very simple formula in an Excel workbook:

=WORKDAY($A2,9,BankHolidays)

This takes the date that sits in cell A2, adds on 9 working days, excluding any Bank Holiday dates that are present in the BankHolidays table. Simple.

But trying to accomplish the same thing in Power BI using DAX or M Lang is not so simple, at least from what I am finding.

I have tried to produce the following DAX but unfortnately I get a widly incorrect date.

10 Day Letter Due =
NETWORKDAYS (
    table1[Date Received],
    DATEADD ( table1[Date Received], 9, DAY ),
    1,
    tblPublicHolidayDates
)

If anyone is able to assist with a simple and ellgant solution to this, it would be very much appreciated.

Thanks,
Paul

[PaulMac]

Thank you @DataInsights 
That did the job perfectly!
Paul

[DataInsights · Accepted solution]

@PaulMac,

Try this calculated column in Table1. It requires a Dates table with columns Is Weekday and Is Holiday.

10 Day Letter Due =
VAR vDaysToAdd = 9
VAR vStartDate =
    Table1[Date Received]
VAR vWorkdaysAfterStartDate =
    FILTER (
        Dates,
        Dates[Date] > vStartDate
            && Dates[Is Weekday] = 1
            && Dates[Is Holiday] = 0
    )
VAR vResult =
    MAXX (
        TOPN ( vDaysToAdd, vWorkdaysAfterStartDate, Dates[Date], ASC ),
        Dates[Date]
    )
RETURN
    vResult