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.
- It is not possible to customize the automatic date tables. By default these only contain Year, Quarter, Month and Date.
- 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:
"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.