Forum Discussion

faustusxanthis's avatar
faustusxanthis
Frequent Visitor
3 years ago
Solved

WORKDAY formula in Power BI with specified holidays

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 du...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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])

     

johnt75's avatar
johnt75
Icon for Super User rankSuper User
3 years ago

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's avatar
User1782
New Member
2 years ago

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.