Microsoft Power BI Cookbook

Date dimension planning and design

1. Look to integrate other calendars, such as a periods or fiscal calendar, into the same date table in the source database:
   • Distinct views can be created to support role playing dimensions. See the There's more... section
2. Identify common date manipulations taking place in the existing reports or by business users in Excel or Power BI, and consider adding a date dimension column to eliminate or simplify this work.
3. Ensure that date columns (for example, Prior Year Date) and number columns (for example, Calendar Year) are stored as date and integer data types, respectively, as this allows arithmetic and DAX functions, such as MAX() and MIN(), to operate without any type conversion.

If the date dimension ...