WORKDAY formula in Power BI with specified holidays

[faustusxanthis]

Hello!

I can't seem to find the right solution to this. 

I have a table (Table 1) which contains a list of cases, target days, and a start date. I wanted to create a column which creates a due date calculated from the start date + target days not including weekends and a list of holiday dates. 
In Excel, it was possible to do it as =WORKDAY($A2,$B2,Ref!A:A) (column D) wherein column A = start date, column B = number of days, Ref!A:A = a separate sheet/table with all the holidays manually input. 

Table 1 looks like this: 

Ref Sheet looks like this: 

How do I create a column similar to Table1's column D in Power BI? 

EDIT: I would specifically need a new column similar to the Excel formula "=WORKDAY($A2,$B2,Ref!A:A)"

[johnt75]

You can create a column like

Due date = 
VAR StartDate = 'Table'[Start date]
VAR NumDays = 'Table'[Num days]
VAR Result = MAXX( FILTER( VALUES('Date'[Date]), NETWORKDAYS( StartDate, 'Date'[Date]) = NumDays ), 'Date'[Date] )
RETURN Result 

You can change the call the NETWORKDAYS to include your holiday table.

[User1782]

This is the greatest answer to the Workday equivalent I have ever seen. Somebody spam this all over all the other overly complicated solutions people come up with, including my own.

[Anonymous]

How to exclude holidays here in this dax...i have one holiday calender table

[Greg_Deckler · Accepted solution]

@faustusxanthis Try:

Due Date Column =
  VAR __Start = [Start]
  VAR __TargetDays = [Target Days]
  VAR __Holidays = { DATE(2022,1,1), DATE(2022,12,25) }
  VAR __Table = 
    ADDCOLUMNS(
      EXCEPT( CALENDAR(__Start, __Start + __TargetDays * 2), __Holidays),
      "__WeekDay", WEEKDAY([Date],2)
    )
  VAR __Table1 = FILTER(__Table, [__WeekDay] < 6)
  VAR __Table2 = 
    ADDCOLUMNS(
      __Table1,
      "__Index",COUNTROWS(FILTER(__Table1, [Date] <= EARLIER([Date]))
    )
RETURN
  MAXX(FILTER(__Table2,[__Index] = __TargetDays),[Date])

[faustusxanthis]

@Greg_Deckler 

Thank you for the prompt response. Just a question though..I have the list of holidays specified in a different sheet/table. How can I reference it in this formula? 

VAR __Holidays = { DATE(2022,1,1), DATE(2022,12,25) }

[Greg_Deckler]

@faustusxanthis Easy, 

VAR __Holidays = DISTINCT('Holidays'[Date])