Ravindra BagaleCourses & study guides मराठी Track your progress

24. Power Pivot and the Data Model

24.5 A Date Table (Calendar)

By Last updated:

Example first. A PivotTable by a text month column puts Nov before Oct (alphabetical) and has no place for a month with no orders. A Calendar table with one row per day from 01-10-2026 to 30-11-2026 (61 rows), a Month Number column and "Mark as Date Table" fixes both.

Date table — in 30 seconds

What: A date table has one row for every date in the period, with no gaps, plus columns such as Year, Month Number and Month. It is related to the Date column of the fact table.

Why: Months sort correctly, every month appears even with no sales, and time-intelligence functions such as DATEADD and TOTALYTD need a proper date table to work.

Tiny example: Calendar from 01-10-2026 to 30-11-2026 = 61 rows. Month sorted by Month Number gives Oct ₹1,005, Nov ₹1,310.

First look at the data (before → after):

Date table: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calendar table (first and last rows)

Date Year Month Number Month
01-10-2026 2026 10 Oct
02-10-2026 2026 10 Oct
03-10-2026 2026 10 Oct
… … … …
30-11-2026 2026 11 Nov

Expected result — PivotTable by Calendar[Month] (sorted by Month Number)

Month Sum of Amount (₹)
Oct 1,005
Nov 1,310
Grand Total 2,315
Before table and formula as text

Before — PivotTable by a text month (sorted A to Z)

Month Sum of Amount (₹)
Nov 1,310
Oct 1,005

Formula / click path

Power Pivot window › Design › Calendars › Date Table › New   (or build the Calendar table on a sheet)
Design › Mark as Date Table › Date column: Date
Home › Sort by Column: sort Month by Month Number

Do it step by step:

  1. On a new sheet type Date in A1, 01-10-2026 in A2, and fill down to 30-11-2026 with Home › Fill › Series (Step value 1).
  2. In B2 type =YEAR(A2), in C2 =MONTH(A2), in D2 =TEXT(A2,"mmm"). Make it a Table named Calendar.
  3. Add Calendar to the Data Model and relate Orders[Date] to Calendar[Date] in Diagram View.
  4. In the Power Pivot window, select the Calendar table and click Design › Mark as Date Table, choose the Date column.
  5. Select the Month column and click Home › Sort by Column, sort by Month Number.
  6. In the PivotTable use Calendar[Month] (not the Orders date). Oct now comes before Nov.

Date table checklist

  1. One row per day, no gaps, covering every date in the fact table (whole months or whole years is best).
  2. A real date column (not text) that is unique.
  3. Related to the fact table's date column (one-to-many).
  4. Marked as the date table.
  5. Text columns such as Month sorted by a number column (Sort by Column).

You can also let Excel build it: in the Power Pivot window, Design › Calendars › Date Table › New creates a Calendar table covering the dates in your model, with Year, Month and more already filled in.

Practice task (try it before you open the answer):

  1. Build the Calendar table for October–November 2026 and relate it.
  2. Count the rows.
  3. Make a PivotTable with Calendar[Month] in Rows and Distinct Count of CustomerID (Orders) in Values.
Answer

61 rows (31 + 30). Distinct customers: Oct 5, Nov 5; Grand Total 6 (a customer who bought in both months is counted once in the total).

Ravindra Bagale's Tip

Make the Calendar cover whole years, even if your data starts mid-year. Year-to-date and last-year measures give strange answers when the calendar starts in the middle.