Date Tables in Power BI

For many entry level Power BI developers, using the built in date table in Power BI will work fine. Behind the scenes Power BI auto creates a date table based on the date values in your data. However, many experienced Power BI developers want more control over their date tables and so choose to create their own.

How to Create a Date Table in Power BI

Creating your own date table in Power BI is simple:

1. Select New Table in the “Table Tools” tab on the ribbon.
2. Power BI prompts you to enter a value for the Date Table. Set it equal to CalendarAuto(1) as seen below: Date Table = CALENDARAUTO(1)
   1. Note: The number 1 that gets passed into the formula represents the start of the fiscal year for the calendar table being created. If the start of your fiscal year is not January, change the value that gets passed in.
3. In the table tools ribbon, select “Mark as Date Table”.
4. Choose the “Date” column in the Date Table Settings modal that appears.
5. Select “OK”

Benefits of a date table

1. Develop and create customizable date hierarchies that can be used in DAX measures.
2. Limit the dates your report takes into account to a specific range of dates.
   1. Note: In order to accomplish this, use the Calendar function instead of using the CalendarAuto function as shown below:
      Date =CALENDAR (DATE(2000;1;1); DATE(2100;12;31))
3. If your data-set contains multiple tables that have date records, its best to create your own date table to join the tables. This gives you an additional level of control over the relationship and the ability to look at (or change) the date table if there’s an issue

Customizing the date table in Power BI is a great way of giving yourself additional control of your report, and one I’d recommend that everyone use as a best practice. It may seem easy to use Power BI’s automatic date table but it will save major headaches down the line if your dataset gets complex.

Learn more about Date Tables in Power BI via Microsoft’s website here.