Calculate working days between dates - excluding holidays

[cottrera]

Hi I need to calculate the working days (exlcuding weekends and holidays) between the Date logged and Date completed (See jobs completed table below) I have a calendar table (see calendar table below) which contains a column to show weekends and a column to show holidays . 

thank you

Jobs completed table
Job reference	Date Logged	Date Completed
5417226	23/01/2019	10/06/2019
5512901	05/03/2019	10/06/2019
5527364	12/03/2019	10/06/2019
5533600	15/03/2019	10/06/2019
5534913	15/03/2019	10/06/2019
5544483	21/03/2019	10/06/2019
5561396	01/04/2019	10/06/2019
5567261	04/04/2019	10/06/2019
5570256	05/04/2019	10/06/2019
5570339	05/04/2019	10/06/2019
5573713	05/04/2019	10/06/2019
5573911	05/04/2019	10/06/2019
5574670	05/04/2019	10/06/2019
5576543	05/04/2019	10/06/2019
5576626	05/04/2019	10/06/2019
5576634	05/04/2019	10/06/2019
5576775	05/04/2019	10/06/2019
5577236	05/04/2019	10/06/2019
5577393	05/04/2019	10/06/2019
5578804	05/04/2019	10/06/2019
5579117	05/04/2019	10/06/2019
5587425	10/04/2019	10/06/2019
5590840	12/04/2019	10/06/2019
5601390	18/04/2019	10/06/2019
5603099	23/04/2019	10/06/2019
5603221	23/04/2019	10/06/2019

Calendar Table
PKDate	is_weekend	is_holiday
03/04/2017	0	0
04/04/2017	0	0
05/04/2017	0	0
06/04/2017	0	0
07/04/2017	0	0
08/04/2017	1	0
09/04/2017	1	0
10/04/2017	0	0
11/04/2017	0	0
12/04/2017	0	0
13/04/2017	0	0
14/04/2017	0	1
15/04/2017	1	0
16/04/2017	1	0
17/04/2017	0	1
18/04/2017	0	0
19/04/2017	0	0
20/04/2017	0	0
21/04/2017	0	0
22/04/2017	1	0
23/04/2017	1	0
24/04/2017	0	0
25/04/2017	0	0
26/04/2017	0	0
27/04/2017	0	0
28/04/2017	0	0

[v-yuta-msft · Accepted solution]

@cottrera,

Create a calculate column on 'Jobs completed' table using DAX below:

Working Days Number = 
VAR Start_Date = 'Jobs completed'[Date Logged]
VAR End_Date = 'Jobs completed'[Date Completed]
RETURN
CALCULATE(COUNT('Calendar'[PKDate]), FILTER('Calendar', 'Calendar'[PKDate] >= Start_Date && 'Calendar'[PKDate] <= End_Date && 'Calendar'[is_weekend] = 0 && 'Calendar'[is_holiday] = 0))

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cottrera]

Great works fine. Thank you for your quick response