> ## Content Index
> Fetch the complete content index at: https://brilaro.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Exclude weekends and holidays in a Power BI report
- URL: https://brilaro.com/exclude-weekends-and-holidays-in-a-power-bi-report/
- Published: 2026-10-06T04:25:24.000Z
- Updated: 2026-10-06T04:28:32.000Z
- Author: Neal
- Tags: Power BI

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

1. **Create a holiday list.** In SharePoint, create a list called `HolidayList` with a `Date` column (Date only, no time). Add every holiday you want to exclude, and add new years as they come.
2. **Import both sources.** In Power BI Desktop, connect to `HolidayList` and to your main data source (called `DataSource`below), which has a `StartDate` and an `EndDate` column.
3. **Set the date types.** In Power Query, change `HolidayList[Date]`, `DataSource[StartDate]` and `DataSource[EndDate]` to the **Date**type, 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

```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

```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.