Count of working days between two date columns, excluding holidays

[Hundoa]

Hello,

I've looked at a number of posts on similar issues, however I still can't get this to work. 

I have a table with work received and closed dates in separate columns. I need to calculate the number of working days between the two dates, excluding and holidays. 

I have a date table set up, which pulls through the holidays from another table and a column which gives a 1 or 0, depending on whether it is a working day or not, using the following logic

Duration 1/0 = IF(OR('Date Table'[Day of Week]=5,'Date Table'[Day of Week]=6),0,IF('Date Table'[Is Bank Holiday]="",1,0))

 

Using DATEDIFF, I can get the number of days between the two date field, however I'm getting stuck on how to filter out the holidays on the date table. 

 

Days between Rec/Closed = DATEDIFF('Workflow Duration'[Received Date],'Workflow Duration'[Closed Date],DAY)+1

Could someone please let me know what I should be doing with the logic to achieve this?

Thank you. 

[Anonymous]

@Hundoa 

Check my example below, pbix also attached. You may add other bank holiday conditions to the filter if you have.

Column = CALCULATE(COUNTROWS('Date table'),
                FILTER('Date table',[Date]>=EARLIER(([Start]))&&[Date]<=EARLIER([Close])),
                FILTER('Date table',WEEKDAY('Date table'[Date],2)<>6 &&WEEKDAY('Date table'[Date],2)<>7))

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@Hundoa , refer two the second page - workday diff of the attached file after signature

Or Refer :https://www.sqlbi.com/articles/counting-working-days-in-dax/