Time Intelligence visualisations allow the user to quickly gain insight into short—and long-term trends, seasonality, and, if the data permits, even scenario forecasting. To get the most out of your time intelligence analysis, you need to start using Date Dimension tables.
Why you should use a Date Dimension tables
Date Tables allow you to specify date parts and events, like a custom fiscal year, or a specific date format, like Year-Month. In addition, Power BI has an inherent pitfall in that it will only consider dates for analysis, which is explicitly stated somewhere in your data model. This means that you risk returning blank values in your calculations if you don’t implement a full date table.
How to implement a Date Dimension Table
Many organisations decide to build their own Date Table from scratch, taking into account custom needs for column formatting, fiscal year periods and holidays. These tables can be built with Excel or another tool of choice, and imported into Power BI. A different option for quick deployment, is to create your own Date Table using one of the following options:
Quick Date Table with DAX: CALENDARAUTO()
The fastest way to get a working calendar of dates, is to create a new table by going to Modeling > New Table, and use the CALENDARAUTO() function.
Customised Date Table with DAX: CALENDAR()
For a more advanced calendar that includes a range of custom columns, you can copy the DAX formula from the following link, and paste into the “New Table” formula: Link
Summary
Date Dimension Tables are absolutely necessary for accurate calculations and analysis of Date variables. Use the methods above to set up your own Date Table. If you have any questions or need more information, please do not hesitate to contact us.
Your Success Starts Here
Partner with Devoteam to leverage award-winning tech expertise, agile execution, and a culture of continuous learning.