Date Tables & the Indian Financial Year

Almost every business question involves time: this month, last quarter, this financial year, the same period last year. Power BI can answer all of them, but only if your model has a proper date table. Indian businesses add one more twist: the financial year runs from 1 April to 31 March, so "Q1" means April to June, not January to March.

In this lesson you will build a date table for Kesar Mart with financial-year columns, sort months in the right order, mark it as the model's date table, and relate it to Sales.

Why not just use the OrderDate column?

Power BI's auto date/time option quietly creates a hidden calendar for every date column, which is why you see a Year > Quarter > Month > Day hierarchy under OrderDate. It is fine for a first experiment, but:

• Its quarters and years are calendar ones (January to December). There is no financial year.
• Each date column gets its own hidden table, which bloats larger models.
• You cannot add your own columns, such as festival weeks or working days.
• Two fact tables (Sales and Targets) cannot share it, so you cannot filter both with one month slicer.

A dedicated date table solves all of these. Microsoft's guidance recommends one for all but the simplest models.

Rules for a date table

For DAX time intelligence functions to work reliably, a date table must:

1. Have a column of type Date with one row per day.
2. Contain no duplicates and no blanks.
3. Be continuous: no missing days between the first and last date.
4. Cover whole years, so it should start on 1 April and end on 31 March for Indian financial years, covering every date in your facts.

Kesar Mart's sales run from April 2025 to June 2026, so the table will cover 1 April 2025 to 31 March 2027: two full financial years.

Building it with DAX

On the Modeling ribbon (or Table tools), select New table and enter:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2025, 4, 1 ), DATE ( 2027, 3, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "Year Month", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Month Year", FORMAT ( [Date], "MMM YYYY" ),
    "Financial Year",
        VAR StartYear = IF ( MONTH ( [Date] ) >= 4, YEAR ( [Date] ), YEAR ( [Date] ) - 1 )
        RETURN "FY " & StartYear & "-" & RIGHT ( FORMAT ( StartYear + 1, "0" ), 2 ),
    "FY Month Number", MOD ( MONTH ( [Date] ) + 8, 12 ) + 1,
    "FY Quarter", "Q" & ( INT ( MOD ( MONTH ( [Date] ) + 8, 12 ) / 3 ) + 1 )
)

How it works:

• CALENDAR returns a one-column table named Date with every day between the two dates.
• ADDCOLUMNS adds a named column for each pair of name and expression.
• Financial Year works out the year in which the financial year started: April onwards belongs to this year's FY, January to March to last year's. VAR stores that value so it can be used twice; variables get a full section in ALL, REMOVEFILTERS, DIVIDE & Variables.
• FY Month Number shifts the months so April is 1 and March is 12. For April: (4 + 8) mod 12 + 1 = 1.
• FY Quarter turns months 1 to 3 of the FY into Q1, 4 to 6 into Q2, and so on.

Check the rows around the year boundary in Table view:

Date       | Month | Financial Year | FY Month Number | FY Quarter
-----------+-------+----------------+-----------------+-----------
30-03-2026 | Mar   | FY 2025-26     | 12              | Q4
31-03-2026 | Mar   | FY 2025-26     | 12              | Q4
01-04-2026 | Apr   | FY 2026-27     | 1               | Q1
02-04-2026 | Apr   | FY 2026-27     | 1               | Q1

The table has 730 rows: 365 days in each financial year (neither February 2026 nor February 2027 has a 29th).

The same table in Power Query

If you prefer to keep all preparation in Power Query, create a Blank Query and open the Advanced Editor:

let
    StartDate = #date(2025, 4, 1),
    EndDate = #date(2027, 3, 31),
    DayCount = Duration.Days(EndDate - StartDate) + 1,
    DateList = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
    DateTable = Table.FromList(DateList, Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(DateTable, {{"Date", type date}}),
    AddFYStart = Table.AddColumn(
        Typed, "FY Start Year",
        each if Date.Month([Date]) >= 4 then Date.Year([Date]) else Date.Year([Date]) - 1,
        Int64.Type
    ),
    AddFY = Table.AddColumn(
        AddFYStart, "Financial Year",
        each "FY " & Text.From([FY Start Year]) & "-" & Text.End(Text.From([FY Start Year] + 1), 2),
        type text
    )
in
    AddFY

Add the month and quarter columns the same way with Add Column > Date menus. Either approach is fine; pick one and use it consistently.

Sort months in financial-year order

Put Month on a chart axis and you get Apr, Aug, Dec, Feb, Jan... because text sorts alphabetically. Fix it once in the model:

1. In Table view, select the Month column.
2. On Column tools, choose Sort by column > FY Month Number.

Now every visual shows Apr, May, Jun ... Mar. Do the same for Month Year using Year Month, so "Mar 2026" comes before "Apr 2026".

Mark as date table and relate it

1. Select the Date table, then Table tools > Mark as date table, and choose the Date column. Power BI checks that the values are unique, not blank and continuous.
2. In Model view, drag Sales[OrderDate] onto Date[Date] to create a many-to-one, single-direction relationship.
3. Once your own date table is in place, turn off auto date/time for the file: File > Options and settings > Options > Current File > Data Load.
4. Use Date columns, never Sales[OrderDate], in slicers and axes.

A matrix with Financial Year and FY Quarter in rows and SUM ( Sales[Amount] ) in values now gives:

Quarters without sales (Q2 and Q3) are simply not shown. March 2026 correctly falls in Q4 of FY 2025-26, not Q1 of 2026.

Common mistakes

• Starting the table on 1 January. Indian FY totals then miss or split months. Start on 1 April.
• Gaps in dates. Building the table from sales dates leaves out days with no sales, and time intelligence functions fail. Generate every day.
• Forgetting Sort by column. Months appear alphabetically.
• Slicing with the fact table's date column. It cannot filter a second fact table. Use Date[Date] and its columns.
• A date/time column with times. 05-04-2025 10:30 does not match the date 05-04-2025. Convert fact dates to type Date in Power Query.

Key points

• A date table has one row per day, no gaps or duplicates, and covers whole (financial) years.
• Build it with CALENDAR and ADDCOLUMNS in DAX or List.Dates in Power Query.
• For the Indian financial year, compute the FY start year (April onwards = this year) and an FY month number where April is 1.
• Sort Month by FY Month Number, mark the table as a date table, and relate Sales[OrderDate] to Date[Date].
• Calendar-based time intelligence is newer and recommended by Microsoft, but still in preview.

"Is Weekend", WEEKDAY ( [Date], 2 ) >= 6,
"FY Month Label",
    FORMAT ( MOD ( MONTH ( [Date] ) + 8, 12 ) + 1, "00" ) & " " & FORMAT ( [Date], "MMM" )