How to Use 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 the modeling tab of the Power BI desktop.
Basic DAX syntax would be:
Date =
ADDCOLUMNS (
CALENDARAUTO(),
"DateAsInteger", FORMAT ( , "YYYYMMDD" ),
"Year", YEAR ( ),
"Monthnumber", FORMAT ( , "MM"),
"YearMonthnumber", FORMAT ( , "YYYY/MM" ),
"YearMonthShort", FORMAT ( , "YYYY/mmm" ),
"MonthNameShort", FORMAT ( , "mmm" ),
"MonthNameLong", FORMAT ( , "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( ),
"DayOfWeek", FORMAT ( , "dddd" ),
"DayOfWeekShort", FORMAT ( , "ddd" ),
"Quarter", "Q" & FORMAT ( , "Q" ),
"YearQuarter", FORMAT ( , "YYYY" ) & "/Q" & FORMAT ( , "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 data in other tables. As you can see this is a fairly simple task and provides you with more flexibility when working with dates.
Questions?
If you need help with Power BI or have questions, contact us and we will be glad to point you in the right direction.
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.