Calendar Dimension Overview

Overview

The date calendars have several goals in mind:

• Combine fiscal and transaction date calendar dimensions
• "Presentation Friendly Fields"
• Power BI friendly

A "presentation friendly" field would be a month, quarter, year with a text qualifier e.g. Q 2 or FY 2001.

Types of Calendar Dimensions

We segmented calendar dimensions into two camps:

1. If it is the "main engine" of the data model such as transaction date
2. If it embellishes a report or analytic such as LiabilityPeriodStartDate

Let's use Vehicle Mileage as an example.

Trip End Date is the main driver of the data model, so we can perform drill downs.  Trip Start Date is not the main driver - just a field that would augment the big picture.

vd_Cal_TripStartDate	vd_Cal_TripEndDate

In some cases, a data model might have several "driver" calendar dimensions.  For instance Sales has one for transaction dates and one for Open Sales Orders Shipping Date (as well as one each for Service Date and Fully Paid Date).

Power (Query/Pivot/BI) Notes

• We performed the sort of certain fields by other columns e.g. Month Name is sorted by the Month Number field.
• We have added running consecutive number fields for year, quarter, month, week, and day using M Language.  Better for comparisons and removes the need to be marked as a "date table".
• All date fields are formatted as MM/DD/YYYY.
• We created consistent hierarchies for drill downs
• We use a particular set of hidden and visible fields
• We have turned off the "Auto date/time for new files" in Power BI.

Our architecture does NOT require that a calendar table be marked as a "date table" in PowerPivot or Power BI. 

Example of the Transaction Date Dimension in the Sales Data Model: