excelisfun - Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 2016

Short Summary

Main recommendation: Use Excel 2016's automatic Date Table feature in PowerPivot (Design > Calendars > Date Table > New) to create a calendar table for your data model instead of building one by hand. The calendar table is needed for advanced DAX work such as time intelligence functions or non-standard quarters, which the simple Group By Date feature cannot handle. The presenter demonstrates the process using 800,000 rows of sales data.

Key Points

• Excel 2016's automatic calendar table was suggested by YouTube user jazzista1967 and replaces a hand-built calendar table.
• Group By Date works for small datasets, but a calendar table is required for advanced DAX and time intelligence functions or custom quarters.
• The generated table includes a unique date column plus Year, Month Number, Month Name, Year and Month, Day of the Week, and day name columns.
• The generated table also creates a date hierarchy that drags into a pivot table as one field.
• The calendar table must be related one-to-many to the date column of the fact table.

Details Worth Noting

• Concrete instructions: Open PowerPivot and choose Manage Data Model. In the Design tab, click Calendars, then Date Table, then New; Excel finds the minimum and maximum dates and builds the table automatically. To refresh after source data changes, click Date Table and choose Update Range. To use it in a pivot table, switch to Diagram View (View > Diagram View), create a one-to-many relationship from the calendar date column to the sales table date column, then create a pivot table on a new sheet and drag the date hierarchy into Rows.
• The presenter's pivot table example had to be tidied: dragging the date hierarchy into Rows expands into Year, Month, and Day; he then tried Month Number in Rows and Year in Columns via More Fields, but corrected it by unchecking Month Number and using Month instead.
• The example data set contains 800,000 rows with sales, date, product, and revenue columns.

Bottom Line

The automatic calendar table in Excel 2016 gives a quick, refreshable date dimension for data models, useful when you need advanced DAX, time intelligence functions, or non-standard quarters.