Creating calendar tables
Another useful feature in Power Pivot is the ability to create a dynamic date or calendar table for your data model.
In our Main Transactions table, we have columns that contain dates (the order dates and delivery dates in the Main Transactions table). However, there are gaps or missing dates in these columns. These gaps will not be ideal for us to use some of the time intelligence formulas. We need to create a series of dates that begin from the earliest date in our dataset all the way to the latest date without missing a date.
Power Pivot allows us to create this by using the Date Table feature under the Design tab. This will result in the creation of a new table that contains Date attributes we can use in our calculations.
To do this, go to Design | Date Table | New.
This should give us a date table with different columns, as shown in the following screenshot:
Figure 4.12 – Creating a calendar table in Power...