Create date tables in Power BI Desktop

Why Do I need a Date table ?

To work with Data Analysis Expressions (DAX) [time intelligence functions], there's a prerequisite model requirement: You must have at least one date table in your model.

What is a Date table ?

• It must have a [Date] column of date (or date/time) type.
• The [Date] column must contain unique values.
• The [Date] column must not contain BLANKS.
• The [Date] column must not have any missing dates (between start/end dates).
• The [Date] column must span full years. A year isn't necessarily a calendar year (January-December).
• The [Date] table must be marked as a date table.

How do I create a Date table ?

• The Auto date/time option.
• Power Query to connect to an existing date dimension table (in a datawarehoure).
• Power Query to generate a date table.
• DAX to generate a date table (easier because DAX includes some built-in intelligence to simplify creating and managing date tables).
• DAX to clone an existing date table.

Should I use the Auto date/time option ? (Recommend: disable and create your own date table)

• Auto date/time option:

  • Delivers convenient, fast, and easy-to-use time intelligence.
  • Be able to work with time intelligence when filtering, grouping, and drilling down through calendar time periods.

• Keep enabled:

  • When you work with calendar time periods.
  • When you have simplistic model requirements in relation to time.
  • When you don't need a single date table design that propagates filters to multiple tables.
  • When you are performing a data exploration or profiling.

• Limitations:

  • Applies to all or none: It can't be selectively enabled or disabled on a column-by-column basis.
  • Calendar periods only: The year begins on January 1 and finishes on December 31. There's no ability to customize the year commencement (or completion) date.

• Customization: It's not possible to customize the values used to describe time periods. Further, it's not possible to add additional columns to describe other time periods, for example, weeks.

• Year filtering: The Quarter, Month, and Day column values don't include the year value. For example, the Month column contains the month names only (that is, January, February, etc.). The values are not fully self-describing, and in some report designs may not communicate the year filter context.

That's why it's important that filters or grouping must take place on the Year column. When drilling down by using the hierarchy year will be filtered, unless the Year level is intentionally removed. If there's no filter or group by year, a grouping by month, for example, would summarize values across all years for that month.

• Single table date filtering: Because each date column produces its own (hidden) auto date/time table, it's not possible to apply a time filter to one table and have it propagate to multiple model tables. Filtering in this way is a common modeling requirement when reporting on multiple subjects (fact-type tables) like sales and sales budget. When using auto date/time, the report author will need to apply filters to each different date column.

• Model size: For each date column that generates a hidden auto date/time table, it will result in an increased model size and also extend the data refresh time.

• It isn't possible to work with auto date/time tables when:

  • Using Analyze in Excel.
  • Using Power BI paginated report Analysis Service.
  • Connecting to the model using non-Power BI report designers.

Apply auto date/time in Power BI Desktop

The purpose of this option is to support convenient time intelligence reporting based on date columns loaded into a model. Specifically, it allows report authors using your data model to filter, group, and drill down by using calendar time periods (years, quarters, months, and days). What's important is that you don't need to explicitly develop these time intelligence capabilities.

When the option is enabled, Power BI Desktop creates a hidden auto date/time table for each date column, providing all of the following conditions are true:

• The table storage mode is Import
• The column data type is date or date/time
• The column isn't the "many" side of a model relationship

How it works Each auto date/time table is in fact a calculated table that generates rows of data by using the DAX CALENDAR](https://learn.microsoft.com/en-us/dax/calendar-function-dax) function. Each table also includes six (+Date) calculated columns: Date, Day, MonthNo, Month, QuarterNo, Quarter, and Year.

Power BI Desktop also creates a relationship between the auto date/time table's Date column and the model date column.

The auto date/time table contains full calendar years encompassing all date values stored in the model date column. For example, if the earliest value in a date column is March 20, 2016 and the latest value is October 23, 2019, the table will contain 1,461 rows. It represents one row for each date in the four calendar years 2016 to 2019. When Power BI refreshes the model, each auto date/time table is also refreshed. This way, the model always contains dates that encompass the date column values.

Displays seven columns: Date, Day, MonthNo, Month, QuarterNo, Quarter, and Year.

Note

Auto date/time tables are permanently hidden, even from modelers. They don’t appear in the Fields pane or the Model view diagram, and its rows don’t appear in Data view. Also, the table and its column can’t be directly referenced by DAX expressions.

The table also defines a hierarchy, providing visuals with a drill-down path through year, quarter, month, and day levels.

Screenshot showing example of what a hidden auto date/time table might look like. Displays two tables: Sales and LocalDateTime table.

Work with auto date/time When an auto date/time table exists for a date column. You can easily identify it because it's adorned with a calendar icon. When report authors expand the calendar object, they find a hierarchy named Date Hierarchy. After they expand the hierarchy, they find four levels: Year, Quarter, Month, and Day.

There is, however, one added capability not supported by regular hierarchies. When the auto date/time hierarchy—or a level from the hierarchy—is added to a visual well, report authors can toggle between using the hierarchy or the date column. This approach makes sense for some visuals, when all they require is the date column, not the hierarchy and its levels. They start by configuring the visual field (right-click the visual field, or select the down-arrow), and then by using the context menu to switch between the date column or the date hierarchy.

Screenshot showing example of a visual field configuration for the OrderDate hierarchy.

The open context menu displays two options allowing the toggling to use the OrderDate column or the Date Hierarchy.

Lastly, model calculations, written in DAX, can reference a date column directly, or the hidden auto date/time table columns indirectly.

Formulas written in Power BI Desktop can reference a date column in the usual way. The auto date/time table columns, however, must be referenced by using a special extended syntax. You start by first referencing the date column, and then following it by a period (.). The formula bar auto complete will then allow you to select a column from the auto date/time table.

Screenshot showing example of entering a DAX measure expression in the formula bar.

The formula so far reads Date Count = COUNT(Sales[OrderDate]. and an auto complete list presents all seven columns from the hidden auto date/time table. These columns are: Date, Day, Month, MonthNo, Quarter, QuarterNo, and Year.

In Power BI Desktop, a valid measure expression could read:

DAX

Copy Date Count = COUNT(Sales[OrderDate].[Date]) Note

While this measure expression is valid in Power BI Desktop, it's not correct DAX syntax. Internally, Power BI Desktop transposes your expression to reference the true (hidden) auto date/time table column.

Configure auto date/time option Auto date/time can be configured globally or for the current file. The global option applies to new Power BI Desktop files, and it can be turned on or off at any time. For a new installation of Power BI Desktop, both options default to on.

The current file option, too, can also be turned on or off at any time. When turned on, auto date/time tables are created. When turned off, any auto date/time tables are removed from the model.

Caution

Take care when you turn the current file option off, because this will remove the auto date/time tables. Be sure to fix any broken report filters or visuals that had been configured to use them.

In Power BI Desktop, you select File > Options and settings > Options, and then select either the Global or Current File page. On either page, the option exists in the Time intelligence section.

Screenshot of the Power BI Desktop Options box.

Why create a date table?

Creating a date table in Power BI is essential for effective data analysis and visualization. A date table serves as a central reference point for time-related calculations and enables you to analyze data based on dates accurately.

Benefits:

• Enhanced time-based analysis: A date table allows you to perform in-depth analysis of your data based on different time periods, facilitating trend analysis, seasonality studies, and year-over-year comparisons. It provides a consistent framework for time-based calculations, such as year-to-date, month-to-date, or quarter-to-date calculations.

• Customizable date hierarchies: With a separate date table, you have more control over the structure of the date hierarchy. You can define custom hierarchies based on your specific requirements, such as fiscal years, quarters, or week-based hierarchies. This flexibility ensures that your reports and visuals follow a standardized and meaningful date hierarchy.

• Consistent reporting and visuals: By using a date table, you ensure that all reports and visuals consistently follow a standard date hierarchy and provide accurate and aligned information. This consistency improves the overall user experience and enables users to compare data across different time periods easily.

• Improved performance: When a table is marked as a date table, Power BI optimizes the query performance by using efficient storage and indexing techniques. This optimization speeds up the calculations and retrieval of time-based data, resulting in faster report rendering and interactivity. It enhances the overall performance of your Power BI reports.

• Simplified calculations: Time intelligence calculations become easier and more intuitive when you have a dedicated date table. Power BI recognizes the marked date table as the primary table for time intelligence calculations, automatically enabling time intelligence functions. You can utilize built-in time intelligence functions or create custom calculations based on the date table, streamlining your analysis workflows.

Why mark a table as a date table in Power BI?

In Power BI, marking a table as a date table serves as a way to explicitly designate a table as the primary date table for the data model. This designation is done through the "Mark as Date Table" option in Power BI Desktop.

By marking a table as a date table, you achieve the following benefits:

• Automatic time intelligence functions: When a table is marked as a date table, Power BI recognizes it as the primary table for time intelligence calculations. Power BI automatically creates and enables time intelligence functions like SAMEPERIODLASTYEAR, TOTALYTD, and PERIODOVERPERIODGROWTH, which simplify your data analysis.

• Simplified relationships: Marking a table as a date table helps Power BI in creating and managing relationships with other tables in the data model. Power BI can intelligently identify and establish relationships based on the marked date table, eliminating the need for manual relationship setup.

• Improved performance: When a table is marked as a date table, Power BI optimizes the query performance by using efficient storage and indexing techniques. This optimization speeds up the calculations and retrieval of time-based data, resulting in faster report rendering and interactivity.

Experts strongly recommend the use of a dedicated calendar table in a data model. And if the calendar table is dynamic, then it has added advantages.

Once the calendar table is created, one should:

Mark it as a Date table Mark as Calendar table.png

Establish One-to-Many relationship (filter direction from calendar table to fact tables) Relationship.png

Points to consider while creating the calendar table:

It should contain all the days for all the years present in the data model. If the fact table contains data for the duration of May 10, 2018, to April 28, 2019, the range of dates in the calendar table is between January 1, 2018, and December 31, 2019. One column should contain one column of DateTime or Date type, with unique values. Mark the calendar as a Date table. Though it is not mandatory, it helps in writing correct DAX.

Benefits:

By using a calendar table, the model becomes more convenient to browse. Grouping various tables at the year, quarter, or month level becomes an easy task. Most of the time intelligence functions in DAX require a separate calendar table.

NOTE: Power BI Desktop has a feature called “Auto Date/Time” (can be accessed through Options & Settings > Options> Data Load). When enabled, Power BI automatically creates a calendar table for each Date or DateTime column in the model. These automatic calendar tables have two significant drawbacks:

Power BI generates one table per date column, which may result in inefficient data models. The tables are hidden and with no modification capabilities. If one needs to add a column for the week, they cannot.

Set and use date tables in Power BI Desktop

Power BI Desktop works behind the scenes to automatically identify columns that represent dates, and then creates date hierarchies and other enabling metadata for your model, on your behalf. You can then use those built-in hierarchies when creating report features like visuals, tables, quick measures, slicers, and so on. Power BI Desktop generates this data by creating hidden tables on your behalf, which you can then use for your reports and DAX expressions.

Many data analysts prefer to create their own date tables, which is fine. In Power BI Desktop, you can specify the table you want your model to use as its date table, and then create date-related visuals, tables, quick measures, and so on, by using that table's date data. When you specify your own date table, you control the date hierarchies created in your model, and use them in quick measures and other operations that use your model's date table.

Screenshot of Power BI Desktop showing the Mark as date table dialog box. You can also select the table and then choose Mark as date table from the Table tools ribbon, shown here.

Screenshot of Power BI Desktop showing the Mark as date table button and options filter.

To set a date table, select the table you want to use as a date table in the Fields pane, then right-click the table and choose Mark as date table > Mark as date table in the menu that appears, as shown in the following imag

Benefits of setting your own date table

• The relationships between the date table and other tables in your model are based on columns of a different data type than Datetime. For example, surrogate keys formatted as yyyymmdd.

Remark:

• When you specify your own date table, Power BI Desktop doesn't auto-create the hierarchies. If you later deselect your date table, Power BI Desktop recreates the automatically created built-in date tables for you, for the date columns in the table.

• When you mark a table as a date table, Power BI Desktop removes the built-in (automatically created) date table. And any visuals or DAX expressions you previously created based on those built-in tables will no longer work properly.

Resources:

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time https://learn.microsoft.com/en-us/power-bi/guidance/auto-date-time https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time https://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query https://blog.crossjoin.co.uk/2013/11/19/generating-a-date-dimension-table-in-power-query https://learn.microsoft.com/en-us/dax/calendar-function-dax https://learn.microsoft.com/en-us/dax/calendarauto-function-dax