Time-intelligence data visualisation allows 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 table
Date Tables allow you to specify date parts and events, like custom fiscal year, or a specific date format like Year-Month. In addition, Power BI has an inherent pitfall 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 Tables are absolutely necessary for accurate calculations and analysis on Date variables. Use the methods above to get your own Date Table setup. If you have any questions or need more information please do not hesitate to contact us.