Create calculated tables

A calculated table is created with a DAX formula that returns a table object, allowing you to duplicate or transform existing model data to produce a new table.

Duplicate a table

A common design challenge in data modeling is handling multiple relationships between tables. For example, the Sales table might have three relationships to the Date table, as shown in the following diagram:

The Sales table stores sales data by order date, ship date, and due date, resulting in three relationships to the Date table. However, only one relationship can be active at a time, as indicated by a solid line in the diagram. The other relationships are inactive and shown as dashed lines.

In our example, the active relationship filters the OrderDateKey column in the Sales table, so filters applied to the Date table affect sales by order date only.

To enable filtering sales by ship date, a new table can be created by duplicating the Date table with the following formula:

Ship Date = 'Date'

This formula creates a new table named Ship Date with the same columns and rows as the original Date table. When the Date table is refreshed, the Ship Date table is also recalculated to remain synchronized. Now you can create an active relationship between Ship Date and Sales tables to allow filtering by ship dates too.

Configure duplicate tables

When you create a calculated table, you need to apply any custom configurations to the new duplicate table you want carried over. For instance, it's a good idea to rename columns so that they better describe their purpose.

In our example, the Fiscal Year column in the Ship Date table can be renamed as Ship Fiscal Year. The Ship Full Date column should be sorted by the Ship Date column and the MonthKey column can be hidden to improve sorting and reporting.

Calculated tables are useful to work in scenarios when multiple relationships between two tables exist, as previously described. However, calculated tables increase the model's storage size and can prolong data refresh times, especially when they depend on other tables.

Create a date table

A good use case for calculated tables is to add a date table to your model. Date tables are required to apply special time filters known as time intelligence.

You can create a calculated date table using the CALENDARAUTO function. The CALENDARAUTO function scans all date and date/time columns in the model to determine the earliest and latest dates, then generates a complete set of dates that span all years in the data. The argument specifies the last month of the financial year; for example, passing 6 sets June as the year end.

Due Date = CALENDARAUTO(6)

The resulting Due Date table contains a single column of dates. The following image shows the Due Date table in data view, with dates sorted from earliest to latest:

If the earliest date in the model is October 15, 2021, and the latest is June 15, 2022, the function returns dates from July 1, 2021, to June 30, 2022. This ensures the table includes complete years, which is required for marking a date table.

Mark as a date table

Once you create a date table, you need to Mark it as a date table in Power BI Desktop. This setting allows you to use time intelligence functions in DAX calculations. When you specify your own date table, Power BI Desktop performs the following validations of that column and its data, to ensure that the data contains:

• Unique values.
• No null values.
• Contiguous date values (from beginning to end).
• Same timestamp across each value for Date/Time data types.

This setting applies to any date tables, either imported or created in Power Query or calculated tables.