digestVidSign in
summary

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

Thumbnail of the video “excelisfun - Excel Magic Trick 1299: Automatic Calendar Table in Data Model, New in Excel 2016”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Technology Science
Tags: technology, science
Views: 2 · Last viewed: 2026-10-16 00:53 UTC

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.
  • The video references a downloadable workbook linked below the video and linked resources on PowerQuery, PowerPivot, and data modeling.

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.