Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
faustusxanthis
Frequent Visitor

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 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: 

faustusxanthis_0-1666790993433.png

Ref Sheet looks like this: 

faustusxanthis_1-1666791044093.png

 

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

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

6 REPLIES 6
johnt75
Super User
Super User

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.

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
Not applicable

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

Greg_Deckler
Super User
Super User

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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) }

 

@faustusxanthis Easy, 

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.