The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 |
Solved! Go to Solution.
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.
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.
Great works fine. Thank you for your quick response