Exclude weekends and holidays in a Power BI report

Share
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

  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 DataSourcebelow), 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 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:

ValueWeekend
1Saturday, Sunday (default)
2Sunday, Monday
7Friday, Saturday
11Sunday only
17Saturday 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.