Holiday Table

What and Why

A holiday table is a table in the data model that list out holidays observed by the company or is needed to be excluded for a date range. We use holiday table in conjunction with date tables or DAX functions like NETWORKDAYS to ensure we are getting the most accurate time intelligence results.

Creating a Holiday Table

Importing

If there is already an holiday list avialble via a database server, please import that into Power BI, just make sure to only import the holiday dates into a single column table as the NETWORKDAYS function will only accept the holiday list in that format. If you do not have a holiday list already built out, a excel spreadsheet can be imported and used instead. An example of a holiday spreadsheet can be found below:

Download Holiday Excel Spreadsheet

Create Custom Table

A holiday list could also be created using DAX. To do this create a new table in Power BI Desktop click on the Modeling Tab and select New table:

This will open up the table formula bar:

You can either create your own date table using DAX or use the code below as a started for your table. This code will create a table with a single column with the dates of holidays:

Holidays = 
DATATABLE(
        "Holiday", DATETIME,
        {
            { "1/1/2020" },
            { "2/17/2020" },
            { "5/25/2020" },
            { "7/4/2020" },
            { "9/7/2020" },
            { "11/26/2020" },
            { "11/27/2020" },
            { "12/25/2020" },
            { "1/1/2021" },
            { "2/15/2021" },
            { "5/31/2021" },
            { "7/5/2021" },
            { "9/6/2021" },
            { "11/25/2021" },
            { "11/26/2021" },
            { "12/24/2021" },
            { "1/3/2022" },
            { "2/21/2022" },
            { "5/30/2022" },
            { "7/4/2022" },
            { "9/5/2022" },
            { "11/24/2022" },
            { "11/25/2022" },
            { "12/26/2022" }
        }
    )