How to Quickly and Easily Create a Date Table in Power BI

August 3, 2023

Date Tables are an essential component of Power BI reports. We’ll explain the fastest ways to create them, and explain why they are necessary in the first place along with many of the key considerations that you should keep in mind when using them.

Power BI Date Tables are an essential tool for developing reports and dashboards. They are the key to fixing many time intelligence problems.

Since before Power BI was launched in 2015, date tables have been a staple in data modeling and business intelligence. Excel even uses them as part of Power Pivot, a predecessor to Power BI. They enable developers and report builds to use Time Intelligence.

Time Intelligence allows reports properly sort in the correct date order and enables calculations like Year to Date, Year over Year, Week over Week etc.

To create a date table in Power BI click on Modeling -> New Table. Enter the formula: Date Table = CALENDARAUTO() to quickly generate a date table based on the range of your existing data. For custom ranges use CALENDAR(DATE(2023,1,1), DATE(2025,12,31)) adjusting the year, month, and days as needed.

While this is the fastest way to create a date table, there are a number of additional considerations to keep in mind.

Let’s dive in!

What are Power BI Date Tables?

Date tables in Power BI are exactly what they sound like, they are a table of dates.

Each row will have its own individual date.

The row below it will be the next date or time in the sequence.

Additional columns can be added to the date time to make subsequent calculations easier in your report. For example, you can quickly add or subtract to a year or month number for fast dynamic filters.

Screenshot of a Power BI date table

It’s worth noting that many of the additional columns in a date table are not strictly necessary. Power BI is a very flexible platform and you can write custom DAX code to achieve many similar filters.

Building additional columns into a date table simply makes it easier to reference different columns.

If you want to use the exact date table as shown above, here’s the DAX code:

Date Table = 
   ADDCOLUMNS (
   CALENDAR (DATE(2023,1,1), DATE(2025,12,31)),
   "Year", YEAR ([Date]),
   "Month", FORMAT ([Date], "MMM"),
   "Month Number", MONTH ([Date]),
   "Quarter", "Q" & FORMAT ([Date], "Q"),
   "Year Quarter", FORMAT ([Date], "YYYY") & "/Q" & FORMAT ([Date], "Q")
)

Why Power BI Requires Date Tables

Date tables are required to enable time intelligence functions. To work properly, Power BI needs a complete, consistent, ordered set of dates. Transactional data will only have dates in which transactions occurred. Date tables are used to fill in the gaps.

There are also some additional benefits and scenarios of using date tables.

  • Custom fiscal year ends – many companies have year end dates other than 12/31. Meaning that Q1, Q2, are a subset of months or dates that will not be known by a default date calendar.
  • Multiple date fields present – if a table has multiple date columns, or multiple tables need to be related on dates it’s more effective to relate them individually to a central date table.
  • Custom date attributes – handle weekdays vs. weekends, adjust for holidays, merge date tables with additional time tables for reporting intervals within every day even if no transaction occurs.

You will find many use cases of date tables as you expand your Power BI knowledge. When faced with strange errors and dates not sorting correctly, you may find that the creation of a date table is the answer that you need.

How to Create a Date Table in Power BI

There are several different ways to create a date table in Power BI. Let’s take a look at what they are, and when to use them.

To start, Go to Modeling in the Power BI ribbon and select New Table.

How to create a new table using DAX formulas in Power BI

This brings you to a DAX formula bar where you can enter the date table formula.

Use CALENDARAUTO() as a fast way to create a Power BI date table

Right click on the newly created Date Table and Mark as a Date Table

Mark a new or existing table and column as a date table.

Users can further define the specific column of the sequential dates, if you create a date table with multiple columns.

Screenshot of a mark as date table column selection screen in Power BI

After the table is created, connect it to other tables in your dataset by adding relationships between the date column and date columns in other tables.

Creating Date Tables with DAX Formulas

There are two DAX formulas you can use to create a date table.

CALENDARAUTO and CALENDAR

here are the differences and the specific DAX formulas used for each one.

CALENDARAUTOAutomatically determines a date range based on data already in the Power BI data model. Users can specify the month that the fiscal year starts.

DateTable = CALENDARAUTO() starts the calendar in January. CALENDARAUTO(4) starts the calendar in April, etc.
CALENDARRequires a specific date range to be defined. Users can hard code dates or use MIN and MAX formulas to define dates dynamically.

DateTable = CALENDAR (DATE(2023,1,1), DATE(2025,12,31)

The biggest difference between the two is how much control you have over the date range that gets generated.

Our preference is to use CALENDAR instead of CALENDARAUTO because of the greater amount of control that it gives us when there are multiple date columns in our data model.

Let’s look at how to create dynamic date ranges for our date table and why you might want to do so.

How to Make a Dynamic Date Table in Power BI

Let’s modify the CALENDAR formula from the example above.

DateTable = CALENDAR (DATE(2023,1,1), DATE(2025,12,31)

Instead of having static dates in our DAX formula, we will set it up to reference a different table in the same Power BI report.

Our table is called CookieSales and looks like this:

Example of a transactional table to support relative dates using a celandar min and max dates function

Update the CALENDAR DAX formula to:

DateTable = CALENDAR(MIN(‘Cookie Sales'[Date]),MAX(‘Cookie Sales'[Date]))

Instead of static dates, it’s replaced with a reference to the ‘Cookie Sales Table‘ [Date Column} for the MIN and MAX separated by a , comma.

Now the date table will match the date range in our dataset or we could force it to begin or and at specific dates in case there is a relevant range for reporting and want to automatically filter out anything outside of the range.

How to Add Additional Columns to a Date Table

You can either modify the CALENDAR function with DATEADD or, you can use Power Query to generate additional columns once the initial date table is generated.

This is our previous example.

This example uses ADDCOLUMNS to tell Power BI to generate new columns.

Each row is a new column.

It works because it first generates a column named “Date” as the default name from the CALENDAR formula.

Each additional row is a separate formula.

In quotes is the new column name. Then separated by a comma is the formula to generate that specific column.

Date Table = 
   ADDCOLUMNS (
   CALENDAR (DATE(2023,1,1), DATE(2025,12,31)),
   "Year", YEAR ([Date]),
   "Month", FORMAT ([Date], "MMM"),
   "Month Number", MONTH ([Date]),
   "Quarter", "Q" & FORMAT ([Date], "Q"),
   "Year Quarter", FORMAT ([Date], "YYYY") & "/Q" & FORMAT ([Date], "Q")
)

This is a quick way to generate a full date table all at once. Another option is to generate a date table and step by step add columns. Even when we write a lot of DAX we think it’s easier to add new columns in the data view editor one at a time.

Here’s how you would do it:

Creating New date columns for a date table in the data view screen of Power BI

From the data view section of Power BI, click on New Column.

You can then type the formula for each new column one by one. It gets you to the same place as DAX but doesn’t require you to create one gigantic formula to decipher later.

Creating Date Tables with Power Query M

It’s worth noting that while most Date Tables are generated in Power BI DAX, it’s possible to also generate them using Power Query formulas. Using Power Query has a few advantages over creating date tables with DAX formulas.

  • Speed – Power Query generally executes operations faster than DAX queries.
  • Add new columns with the Power Query Editor – A limitation with DAX date tables is the inability to edit them in the Power Query editor. By creating a date table with Power Query, you can add and edit columns in a familiar editor for more complex logic.

To start, create a new blank table in Power BI. This function lets you create a table and enter data. It’s super useful for copying and pasting data from Excel too!

We usually type in some place holder data in column 1 like “123”

Add a Custom Column

We used the following Power Query formula to create a Dates column that starts on 1/1/2023 and ends 365 days later.

List.Dates(#date(2023, 1, 1), 365, #duration(1, 0, 0, 0))

The #duration portion of the formula lets you define days , hours , minutes , seconds

The final formula will look like this.

Creating a custom date table in Power BI using Power Query Editor and a Power Query M Formula

Once complete click the two sided arrow at the top of the new Dates column to expand it down into rows.

Check out this video for even more great information on working with date tables in Power Query:

Importing a Custom Excel Date Table into Power BI

A widely overlooked alternative to creating a custom date table, is to import one from a separate dataset. For example, Excel users might find it quicker and faster to create their own date table using Microsoft Excel.

In this example, we used a Microsoft Excel formula for today() along with a column for year() month() and day() pointed at Column A to create a date table incredibly fast using formulas that we are already familiar with.

Creating a custom date table in Microsoft Excel to import into or connect to Power BI

After you setup a date table in Excel, you can import it into Power BI as its own data source. You could also go to the Power BI ribbon, and click Enter Data to copy and paste it in.

Screenshot of the enter data button to quickly copy and paste an Excel date table into Power BI

Note: Doing this will not make your date table dynamic. The Excel sheet or manually entered data would need to be periodically updated.

Going this route does have some benefits though, many companies have departments that already maintain custom calendars like the payroll department. It’s easier to copy and paste at times than to re-create logic in another system.

If you are manually generating date tables outside of Power BI, Microsoft publishes some guidelines for the requirements necessary to turn a column into a date column.

Using a SQL Date Table in Power BI

Some organizations already maintain custom date tables as part of their ETL process. If you’re connecting to data sources like Microsoft SQL Server or a data warehouse, check with the department that manages those resources and see if there’s a date table that you can directly connect to.

This will prevent you from having to recreate custom logic in Power BI and ensures that your reports will tie out to other sources of data within your organization.

Not all organizations will have this as an option, but it’s certainly worth looking into!

What is the Power BI Auto date/time Option?

An area of confusion with date tables is that Power BI has an option under File > Options and Settings > Global > Data Load > Time Intelligence > Auto Date/Time for New Files.

The feature is enabled by default and creates a hidden date table for all of the date and datetime columns in your data model.

Screenshot of auto date/time intelligence option in Power bI.

This is the information used to create a date hierarchy.

Date Hierarchies are the function that groups days into months, quarters and years automatically.

Auto Date’s that are generated as part of a Power BI do not give you many options and can sometimes get confused. For example, having multiple date columns in a dataset can cause problems, and complex data models can break without having relationships between date fields properly defined.

In many cases, you will want to create and manage your own date table if you begin to experience odd sorting or bad behavior with time intelligence functions.

Some developers will create a data model as one of the first steps to any Power BI file because it gives them the control to avoid many common date problems.

If you find that you are always creating custom date tables, it may be worth turning of auto date/time to prevent any confusion for other users.

Conclusion

Date tables are a necessity to accurately calculate changes between various time periods, support custom fiscal year ends, and to ensure proper date sorting within charts and graphs.

You can use a CALENDARAUTO or CALENDAR DAX formula to quickly create a date table.

CALENDAR provides users with the greatest flexibility. CALENDARAUTO is faster and thoughtless to deploy.

For more complex date tables, you should explore creating one with Power Query to create additional custom columns using the Power Query Editor.

If you have lots of custom calculations that are used across repeated reports, consider ways to create a date table once and import it into many different Power BI reports. You may be able to create and maintain a date table in Excel or leverage pre-existing date tables in SQL data sources that are managed by a data engineering team at your business.

Scroll to Top