Create Date/ Calendar table

This article provides information on how to create a date/calendar table.

Detailed Information

Whenever a dataset has dates, it is better to create a date dimension table for performance reasons. Here are the steps to crate a date table:

1. In Power BI Desktop, go to File > Options and settings > Options.

   

2. Under "Data Load", uncheck the box for "Auto date/time" and click "OK.

3. If you know when the start date and end date for the dataset are, that's good. If not, check the data and find the earliest date and latest date on the dataset. (You can create a simple visual to find those dates.)

4. Go to the "Modeling" tab and click "New Table." Type Calendar= CALENDAR(DATE(start date with year, month, date), DATE (end date with year, month, date). In the example below, Calendar= CALENDAR(DATE(2010, 01, 01), DATE (2020, 12, 31), was used.

   

5. Go to the Data pane and click the three dots next to the Calendar table you just created. Then, go to Mark as date: table > Mark as date table.

6. Choose "Date" under the "Date column" and click "OK."
   

7. Create a new column by right-clicking the field. Type Year = FORMAT('field name, "yyyy").
   

8. You can repeat the same process for the month and day columns. For more FORMAT options, go to https://docs.microsoft.com/en-us/dax/format-function-dax.
       

9. Go to the Model pane to create the relationships with the existing table.