11. Data Modelling

11.5 Date Table in Power BI with Example (April–March FY)

By Ravindra Bagale · Last updated: 6 October 2026

Start with a problem

Salman's month chart skips days with no orders, and his YTD measure restarts in January. There is no Date table.

Explain simply

A Date table has one row for every day, with no gaps, plus Month and FY columns. Mark it as the date table and relate it to Orders.

Show the data

Order dates in the practice file (gaps between them):

About this data: This table uses columns: Order Date, Orders. This table uses columns: Date, Month Name, FY Month No, FY. This table uses columns: Month Name, Total Sales (₹), Sales YTD (₹). This table uses columns: Order Date, Amount (₹). Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).

Demonstrate

Show the result

Your result should look like this (fictional sample):

Explain what happened

CALENDAR produced every day of FY 2026-27. Marking it told Power BI to use it for time intelligence, and the "31/3" year-end made YTD restart in April instead of January.

Explore further

• 2028 is a leap year, so FY 2027-28 has 366 days. CALENDAR handles that for you.

Detailed reference for this lesson:

First look at the data (before → after):

Date = CALENDAR(DATE(2024,4,1), DATE(2026,3,31))
FY = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
Table tools › Mark as date table

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

Look: 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.

Fix common mistakes

Practise independently

Think like a professional

One Date table, marked, with FY columns, owned by the model and reused by every report page.

Ravi Sir's Cards for this lesson

Revision cardInterview card

मराठी cards