In this article we’ll discuss what the calendar table looks like and how to use it in Power BI model.

Calendar table is the most important part of Power BI model. Every data table usually contains dates, which means we can connect it to calendar table and filter or aggregate it using date fields (months, days, years etc.). For example, we can check the sum income in September, or cost in 2019.

Power BI recognizes dates in our data and generates a hidden calendar table for every date column in our data. Usually we have more than one date filed in our table, for example Date, OrderDate and PaymentDate. Power BI would generate three separate hidden calendar tables in this case, one for each date field. This would slow down our model. Good practice is to disable automatic calendar tables in settings and to make one ourselves. We can turn off this setting on File > Options > Data Load > Auto date/time for new files.

Turning off the functionality that generates a calendar table for each date field in our data.

You can find more on good practice in setting Power BI Desktop in article Preparing Work Environment in Power BI.

What calendar table looks like

Calendar table consists of all the dates from earliest to latest year that appears in the data model. For example, if earliest and latest year in our model are 2010 and 2020, our calendar table will consist of all the dates between 1/1/2010 and 12/31/2020, even if all the dates don’t appear in our data. Table will also have date attributes like day, month, quarter, year, etc.

Calendar table.

How to use calendar table

Connecting tables: filtering and aggregating

There are two types of tables: fact tables and dimension tables. Fact tables usually contain all the records of a business, for example all the items sold in a supermarket. Dimension tables describe the data in fact tables with attributes like where, who, when, etc.

Calendar table is a dimension table. We connect it to fact table and use it to filter and aggregate data in a certain time period. If we have multiple fact tables, we can connect to all of them using one calendar table to filter more fact tables at once, for example Actual and Plan in September 2019.

Calendar table is used to filter big fact tables.

Time intelligence functions

There are many time analysis functions available in Power BI. We can use function CALENDAR(StartDate, EndDate), that creates a table of all the dates between StartDate and EndDate. Another useful function is CALENDARAUTO(), that recognizes earliest and latest date in our data model and creates a table of the dates in between.

The best calendar table is one we make ourselves. In our next article, Dynamic Calendar in Power Query, we will show how we can make our own calendar tables and avoid common problems that occur with functions like CALENDARAUTO().