Exclude weekends and holidays in a Power BI report
Power BI has no built-in idea of a "business day." If you subtract a start date from an end date, weekends and public holidays get counted too. The fix is to keep holidays in a separate list and count only the weekdays that are not on it. Below are two ways to do it.
Before you start
- Create a holiday list. In SharePoint, create a list called
HolidayListwith aDatecolumn (Date only, no time). Add every holiday you want to exclude, and add new years as they come. - Import both sources. In Power BI Desktop, connect to
HolidayListand to your main data source (calledDataSourcebelow), which has aStartDateand anEndDatecolumn. - Set the date types. In Power Query, change
HolidayList[Date],DataSource[StartDate]andDataSource[EndDate]to the Datetype, not Date/Time. SharePoint stores dates in UTC with a time part, and a leftover time can stop a holiday from matching.
Option 1: Calculated column with CALENDAR
Add a calculated column to DataSource:
DAX
Working Days =
VAR StartDate = DataSource[StartDate]
VAR EndDate = DataSource[EndDate]
RETURN
IF (
NOT ISBLANK ( StartDate ) && NOT ISBLANK ( EndDate ) && StartDate <= EndDate,
COUNTROWS (
FILTER (
CALENDAR ( StartDate, EndDate ), // one row per day in the range
WEEKDAY ( [Date], 2 ) <= 5 // Monday (1) to Friday (5)
&& NOT [Date] IN VALUES ( HolidayList[Date] ) // not a holiday
)
)
)How it works: CALENDAR builds a temporary table with one row for each day between the start and end dates. WEEKDAY(..., 2) numbers the days Monday = 1 through Sunday = 7, so <= 5 keeps weekdays only. Any day found in HolidayList is removed, and COUNTROWS counts what is left.
Notes:
- The count includes both the start and end dates. Subtract 1 if you need the days between them.
- A holiday that falls on a weekend is not subtracted twice.
- The column returns blank when either date is missing or the end date is before the start date.
Option 2: NETWORKDAYS
Newer versions of Power BI include NETWORKDAYS, which does the same job in one function:
DAX
Working Days =
IF (
NOT ISBLANK ( DataSource[StartDate] ) && NOT ISBLANK ( DataSource[EndDate] ),
NETWORKDAYS (
DataSource[StartDate],
DataSource[EndDate],
1, // weekend = Saturday and Sunday
VALUES ( HolidayList[Date] ) // dates to exclude
)
)How it works: NETWORKDAYS counts the days from start to end (both included), skips the weekend days set by the third argument, and skips any date in the holiday table.
Changing the weekend
The third argument sets which days count as the weekend:
| Value | Weekend |
|---|---|
| 1 | Saturday, Sunday (default) |
| 2 | Sunday, Monday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 17 | Saturday only |
Note: Unlike Option 1, NETWORKDAYS returns a negative number when the end date is before the start date.
Which should I use?
- Use Option 2 if your Power BI version supports
NETWORKDAYS. It is shorter, faster and makes custom weekends easy. - Use Option 1 if you need to support older versions, or want extra rules, such as skipping half-days or office closures from another table.
Tip: Use a calculated column, not a measure, when you need a working-day count on every row (for SLAs, ticket ages or turnaround times). The value is worked out once when the data refreshes, not every time a visual loads.