Creating Calendar Table in Power BI using DAX Functions: 3 Easy Steps

Using a calendar table in reporting improves efficiency and ensures consistent data, as it avoids the need to compute date-related data on the fly.

Although creating date data may seem simple at first, it becomes more complex as reporting needs grow, making a calendar table essential for development and maintenance.

In Microsoft Power BI, a calendar or date reference table is often required to organize data, especially if the data lacks detailed date information like month, quarter, or year.

This article explains how to use DAX functions like CALENDAR and CALENDARAUTO to create a Power BI Calendar Table, set up relationships between the Date Table and other columns, and discusses the benefits and limitations of these functions.

What is Power BI?

Power BI is a set of Business Analytics tools that allows you to share insights throughout your company. Microsoft’s Power BI is a Business Intelligence tool. Users may use it to examine data from a variety of sources and create reports and dashboards.

Power BI can connect to the most popular databases outside of the Microsoft environment and create easy and interactive dashboards from them. It can be used as standalone desktop software or as a fully managed Cloud-based web service.

While the Power BI Desktop is a free download, the Power BI Service is a subscription-based service that charges customers based on their usage. Power BI Mobile is now available from Microsoft for those who want to keep an eye on their data while on the go.

Key Features of Power BI

Power BI is a frontrunner among many other BI tools. It has proven to be a reliable and user-friendly Data Analysis and Visualization tool. Let’s discover some of the remarkable features it provides:

• Real-Time Dashboards: As data is sent or streamed in, Dashboards update in real-time, allowing viewers to instantly solve issues and uncover opportunities. Real-time data and visuals can be shown and updated in any report or dashboard.
• Customized Visualization: Companies can easily leverage the custom visualization library and create a visualization that meets their requirements. Analysts can generate highly customizable visuals for their next Power BI report by using open-source data-viz modules from R and Python.
• Get Data Feature: Power BI “Get Data” feature that lets you choose from a variety of data sources, including On-Premise or Cloud-Based, Unstructured, or Structured data, among others.
• DAX Functions: The Power BI DAX(Data Analysis Expressions)  are predefined codes for performing analytics-specific data tasks. Currently, the Power BI function library has over 200 functions. In this article, you will learn more about the Date/Time DAX functions to create a Power BI Calendar Table.
• Robust Security: Power BI leverages Active Directory to set up access to the control panel, and the business will only use Microsoft solutions through this panel. Power BI is built for developing security that allows the team to provide access in a very controlled manner, in addition to providing a typical security layer. 
• Leverages AI: Users of Power BI can employ image recognition and text analytics, as well as develop Machine Learning models and link to Azure Machine Learning.

Introduction to Power BI Calendar Table

A date column can be found in almost every data source or database. How to construct a link between dates in multiple tables is a typical difficulty faced by many beginners. Adding a “Calendar Table” or “Date Dimension” to your Power BI model overcomes this problem. You can link all of your data fields with dates easily.

You can combine the insights into a single chart by using date relationships to connect the various data columns. This enables you to do precise comparisons as well as segment the data using the Calendar Table’s various date period types such as year, month, day, etc. If you wish to add date period categories to the Calendar Table, you only need to do so once. Calendar tables, also known as Date Dimension Tables, are commonly used in Databases and Data Warehouses. The Calendar Table is an extremely useful tool that can help you save a lot of time.

What are DAX Functions?

Data Analysis Expressions (DAX) is a collection of functions and operators that can be used to create formulae and expressions in Microsoft SQL Server Analysis Services, Excel Power Pivot, and Power BI Desktop. Every DAX function applies a specific operation on the values in an argument.

In a DAX formula, you can specify several arguments. New functions and functionality are added to DAX regularly to accommodate new features. Service, application, and tool upgrades, which are usually done periodically, contain new features and changes.

Some of the Power BI DAX functions are:

• Time Intelligence Functions: These functions assist you in creating computations that make use of the built-in calendar and date knowledge. You can make meaningful sales, inventory, and other comparisons over similar time periods by combining time and date ranges with aggregations or other calculations.
• Date & Time Functions: These are used to do calculations on date and time values.
• Logical Functions: These are used to logically assess an expression or argument and return TRUE or FALSE depending on whether or not the condition is fulfilled.
• Mathematical & Trigonometric Functions: These are used to conduct a variety of mathematical operations on the values specified.

There are many more DAX functions supported by Power BI. Refer to DAX Function Reference documentation to learn more about other DAX functions. 

Different DAX Functions to Create a Power BI Calendar Table

You can create a date table in your model by leveraging the CALENDAR or CALENDARAUTO DAX functions. Each function returns a date table with a single column.

The generated table may then be expanded with calculated columns to meet your date interval filtering and grouping needs.

Let’s understand the syntax, parameters, and examples of CALENDAR and CALENDARAUTO DAX functions below.

1) CALENDAR Function

It’s used to set a date range. It returns a table containing a continuous collection of dates with a single column named “Date”. The dates in the range are from the start date to the finish date, inclusive of those two dates.

Syntax:

CALENDAR(<start_date>, <end_date>)

Parameters:

• start_date: Specify the start date.
• end_date: Specify the end date.

Example:

The following DAX formula returns a table with dates between March 1st, 2021, and August 31st, 2021.

CALENDAR (DATE (2021, 3, 1), DATE (2021, 8, 31))

2) CALENDARAUTO Function

It is used to automatically include all dates in the model. You don’t have to worry about extending the table. When a data refresh is completed, the table is recalculated.

Syntax:

CALENDARAUTO([fiscal_year_end_month]) 

Parameters:

• fiscal_year_end_month: Any DAX expression that returns a number between 1 and 12 as an integer. If not given, defaults to the value set in the current user’s calendar table template, if one exists; otherwise, it defaults to 12.

Example:

The DAX expression below generates a basic calculated date table.

ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] )
)
ORDER BY [Date] ASC

Steps to Create a Calendar Table in Power BI using DAX Functions

So, you’ve learned the basics of Power BI and DAX. Now you’re ready to start applying what you’ve learned into creating some insightful reporting. Follow the steps below to create a Power BI Calendar Table using the CALENDAR DAX function.

• Step 1: Disable the Time Intelligence Auto Date/Time Feature
• Step 2: Create a Power BI Calendar Table
• Step 3: Build the Relationship between Tables

Step 1: Disable the Time Intelligence Auto Date/Time Feature

To load auto date/time, Power BI provides a built-in function for Time Intelligence. You should use a date table when working with huge data sets and several tables. So, follow the steps below to disable the Auto Date/Time Feature:

• Click on File. Then navigate to Settings → Options.
• Next, on the Options page, under the Current File options, click on Data Load.
• Now, uncheck the Auto Date/Time box under the Time Intelligence heading as shown below.

Step 2: Create a Power BI Calendar Table

Now that you turned off the automatic loading of Date/Time, let’s create a new Power BI Calendar Table. Follow the steps below:

• From the left menu, click on Data View and then select the Modeling tab as shown below.

• To create a new Date Table enter the following DAX expression and then press Enter:

Date = Calendar(Date(2017,1,1), Date(2018,12,31)

This Calendar DAX formula will create a date table with a date from 1st January 2017 to 31st December 2018.

• To create a new column each for month, year and quarter you can click on a New Column button on the left side and enter the following DAX expressions separately:

Month = Format(‘Date'[Date], “MMM yyyy”)
Quarter = Year(‘Date'[Date]) & “-Q” & Format(‘Date'[Date], “q”)
Year = Format(‘Date'[Date], “yyyy”)

Your Power BI Calendar Table will look something like this:

In addition to creating new columns, you can sort your data or perform other functions. Explore this documentation to know more.

Step 3: Build the Relationship between Tables

Now suppose you want to relate this Power BI Calendar Table with your other table, say, the Sales table. You can do this by following the steps below:

• From the left menu, click on the Relationships icon.
• Next, select the Home tab and click on Manage Relationships as shown below:

• A pop-up window will appear. Click on New.
• From the drop-down lists, select the Date Table and click on the Date Column. Then select the Day Column in Sales Table as shown below:

• Now, you can choose the desired cardinality from the drop-down and select Ok. Your tables will be linked together as shown below:

Great Work! You have understood the basics of how to create and link your Power BI Calendar Table. Here, you used the CALENDAR DAX function to create a new Date Table. You can also use the CALENDARAUTO DAX function. Refer to this article to learn more.

Limitations of Power BI CALENDAR & CALENDARAUTO DAX Functions

Limitations of Power BI CALENDAR Function:

• If the start date is larger than the end date, an error is returned.
• When used in computed columns or row-level security (RLS) rules, this function is not supported in DirectQuery mode.

Limitations of Power BI CALENDARAUTO Function:

• If the model does not contain any datetime values that are not in calculated columns or calculated tables, an error is returned.
• When used in computed columns or row-level security (RLS) rules, this function is not supported in DirectQuery mode.

Benefits of Creating a Power BI Calendar Table

There are several compelling reasons to build your own Power BI Calendar Table. Some of these are:

• By obtaining key columns of data from the Power BI Calendar Table, you can use Power BI’s filtering capabilities to make your reports quick and easy to read.
• Using Power BI Calendar Table you can filter your reports by attributes like Year, Month, and any other aggregation of time you require for your business across as many data tables as you need.
• Create your own unique time view that differs from a regular calendar (for example, a 4/4/5 calendar) or your own definition of a financial year.
• Create notions such as a Power BI Calendar Table for time intelligence. These can be used to let a user pick a time horizon from a slicer and have the report change automatically to reflect that time period.

Conclusion

This article highlighted the various DAX functions to create a Power BI Calendar Table. You learned about the CALENDAR and CALENDARAUTO functions.

In addition, you also understood the steps to create a Power BI Calendar Table using the CALENDAR function and how you can set up relationships between the Date Table and other columns.

At the end of this article, you explored some of the limitations of these functions. Moreover, you understood the various benefits of creating a Power BI Calendar Table.