Skip to main content

Power BI Time Intelligence

By default Power BI automatically creates one date table for each date column in our model. If our model contains multiple date columns across multiple tables then there will be multiple automatic date tables created.

The automatic date tables are good in simple models however there are some limitations.

  1. It is not possible to customize the automatic date tables. By default these only contain Year, Quarter, Month and Date.
  2. A Separate automatic date table for each date column in the model. This makes it hard to build reports that slice multiple tables.

It is a good idea to turn off Auto Date/Time to make working with dates in your data model easier.

To turn off Auto Date/Time Feature go to File >> Options and Settings >> Options Dialog >> Data load or Current file Data load.

A better way to deal with dates is to build your own date table using DAX and using a calculated table. Using the CALENDARAUTO() function allows you to create a simple date table. The CALENDARAUTO function returns a table with a single column named date that contains a contiguous set of dates. The range of dates is calculated automatically based on data In the model.

This would be done in modeling tab of the Power BI desktop.

Basic DAX syntax would be:

Date =
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date]),
"Monthnumber", FORMAT ( [Date], "MM"),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q") )

It is always a good idea to mark it a date table.

You can then create a relationship between the date in the date table and date in other tables. As you can see this is a fairly simple task and provides you with more flexibility when working with dates. 

If you need help with Power BI or have questions, contact us and we will be glad to point you in the right direction.

Over 25 Years Experience

At Prophet we understand that software is just a starting point and we’re focused on delivering solutions that drive accountability, efficiency and growth. Whether you’re outsourcing or looking for someone to work with your team, it’s important to have a partner you have confidence in.

Contact Us Today!