Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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.
Could someone please let me know what I should be doing with the logic to achieve this?
Thank you.
Solved! Go to 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/
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.
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |