11. Data Modelling

11.5 The Date Table

By Ravindra Bagale · Last updated: 25 September 2026

Time analysis (YTD, previous year, month-on-month) needs a proper date table:

• one row per day, no gaps, covering full years of your data;
• a column of type Date with unique values;
• related to the date columns in fact tables.

Option 1: CALENDAR

CALENDAR(<start_date>, <end_date>) returns a single-column table named Date with every date in the range.

Date =
ADDCOLUMNS(
    CALENDAR(DATE(2023, 1, 1), DATE(2026, 12, 31)),
    "Year", YEAR([Date]),
    "Quarter", "Q" & QUARTER([Date]),
    "Month Number", MONTH([Date]),
    "Month Name", FORMAT([Date], "MMMM"),
    "Month Short", FORMAT([Date], "MMM"),
    "Year Month", FORMAT([Date], "YYYY-MM"),
    "Day Name", FORMAT([Date], "dddd"),
    "Weekday Number", WEEKDAY([Date], 2),
    "Is Weekend", WEEKDAY([Date], 2) > 5
)

Create it with Modeling › New table (or Table tools › New table).

A dynamic range based on the fact table:

Date =
CALENDAR(
    DATE(YEAR(MIN(Orders[Order Date])), 1, 1),
    DATE(YEAR(MAX(Orders[Order Date])), 12, 31)
)

Option 2: CALENDARAUTO

CALENDARAUTO([fiscal_year_end_month]) scans all date columns in the model (excluding calculated columns/tables) and builds full years covering them. For an Indian financial year ending in March:

Date = CALENDARAUTO(3)

Option 3: Build it in Power Query or the data warehouse

Many teams create the date table in the source database or in Power Query (M), especially when the same calendar is shared across reports. This is equally valid.

Fiscal year columns (Indian FY: April–March)

Fiscal Year =
VAR m = MONTH('Date'[Date])
VAR y = YEAR('Date'[Date])
RETURN
    IF(m >= 4, "FY " & y & "-" & RIGHT(y + 1, 2), "FY " & (y - 1) & "-" & RIGHT(y, 2))

For 14-03-2025 this returns FY 2024-25; for 14-04-2025 it returns FY 2025-26.

Festival column (Indian context)

Quick-commerce orders in our sample data rise sharply around festivals such as Diwali and Ganeshotsav. Festival dates change every year, so keep a small table Festivals (columns Date and Festival, typed in with Home › Enter data or maintained in Excel) and bring the name into the Date table as a calculated column:

Festival =
LOOKUPVALUE(Festivals[Festival], Festivals[Date], 'Date'[Date])

Is Festival Day = NOT ISBLANK('Date'[Festival])

Later you can write measures such as CALCULATE([Total Orders], 'Date'[Is Festival Day] = TRUE()) or compare festival weeks year on year (Module 13.16).

Mark as date table

He bagha: select the Date table › Table tools › Mark as date table › choose the Date column. Power BI validates that the column is of Date type, has unique values and no blanks.

Why mark it?

• Time intelligence (कालावधीनुसार विश्लेषण) functions work reliably with your own calendar.
• Power BI stops creating hidden auto date/time tables for this table.

Sort Month Name by Month Number

By default, text months sort alphabetically (April, August, December…). Fix: select Date[Month Name] › Column tools › Sort by column › Month Number. Do the same for Day Name (sort by Weekday Number).

Date table shivay time intelligence nahi – he vakya lihun theva, mitrano.