5. Dates table: Unlocking Time Intelligence
To create a data model that takes advantage of all Power BI time intellingence functions, we need to first create a continuos Dates table, spanning the full time period of our data. To do this, I created a date table running from the start of the year containing the earliest date in the Orders['Order Date'] column to the end of the year containing the latest date in the Orders['Shipping Date'] column. These table contains the following columns, added in Data View > New Calculated Column, using DAX formulas:
| Calculated Column | DAX measure |
|---|---|
| Dates | CALENDAR(MIN('Orders'[Order Date]), MAX ('Orders'[Shipping Date])) |
| Day of Week | FORMAT([Date], "dddd" ) |
| Month Number (i.e. Jan = 1) | MONTH(Dates[Date]) |
| Month Name | Dates[Date].[Month] |
| Quarter | QUARTER(Dates[Date]) |
| Year | YEAR(Dates[Date]) |
| Start of Year | STARTOFYEAR(Dates[Date]) |
| Start of Quarter | STARTOFQUARTER(Dates[Date]) |
| Start of Month | STARTOFMONTH(Dates[Date]) |
| Start of Week | Dates[Date] - WEEKDAY(Dates[Date],2) + 1 |
The Dates table should look like the one below.
Finally, we need to create a date hierarchy inside the dates table. This will allow the user to drill down into our data and perform granular analysis within the report. The hierarchy should be: Start of Year>Start of Quarter>Start of Month>Start of Week>Date.
To add hierarchy, right clonk on the table of interest > select 'Create hierarchy' from dropdown menu. To add column to the hierarchy, right click on each column > select 'Add to hierarchy'.
The dates hierarchy should look as follows:
