Forum Discussion

PaulMac's avatar
PaulMac
Icon for Helper IV rankHelper IV
2 years ago
Solved

Converting a simple Formula to Work in Power BI

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

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    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

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    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