DATEDIFF function in DAX allows you to count the difference between 2 dates in terms of DAYS….(or even HOURS , MINUTES , YEARS etc)
But what if you want to count only BUSINESS or WORKING DAYS between the 2 dates
We have the following Data…
| StartDate | EndDate |
| 01-Jan-18 | 13-Jan-18 |
| 09-Jan-18 | 18-Jan-18 |
| 18-Jan-18 | 31-Jan-18 |
| 22-Jan-18 | 28-Jan-18 |
| 02-Feb-18 | 15-Feb-18 |
| 06-Feb-18 | 17-Feb-18 |
| 15-Feb-18 | 28-Feb-18 |
| 28-Feb-18 | 08-Mar-18 |
| 10-Mar-18 | 13-Mar-18 |
| 22-Mar-18 | 06-Apr-18 |
DATEDIFF would give us the Total days Count —or —-difference in Days
Count_Days = DATEDIFF ( TableName[StartDate], TableName[ EndDate], DAY ) + 1
SOLUTION TO COUNT BUSINESS / WORKING DAYS ONLY
If we have a table of dates between StartDate and EndDate, we can add a column to it called WeekDay and then count only rows from Monday to Friday (Weekday 1 to 5).
Following Code will solve the problem
Count Business Days = VAR MyDates = ADDCOLUMNS ( GENERATESERIES ( TableName[StartDate] + 1, TableName[ EndDate] ), "Day", WEEKDAY ( [Value], 2 ) ) RETURN COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) )
Here it how it works
For each ROW
1) GENERATESERIES creates a temporary table of all the dates between Start Date and End Date
2) ADDCOLUMNS simply adds a column to above table…inserting day of the week i..e. a number from 1 to 7
3) Finally we count the rows in the above table, excluding WeekEnds
Click here to download the pbix File