Guides

How to Create a Date Table in Power BI

By Ravindra Bagale, Power BI & Data Analytics trainer

Real example first: Swiggy orders by month

Suppose we are at Swiggy. We have an Orders table with one row per order. Each row has an OrderDate and an Amount.

The manager asks: "Show me sales for every month, and this year's total so far." There is a problem. No order happened on many days. If we use OrderDate directly, those days and months simply do not exist. Time functions like TOTALYTD also need a full list of dates with no gaps.

So we build a separate table that has every single date, one row per day. This is called a date table (some people call it a calendar table).

What is a date table in Power BI?

A date table is a table with one row for each day, from the first date in your data to the last date. It has no gaps. Other columns describe each day: year, month name, month number, quarter, weekday.

Why do we need it?

1. Every month shows in a chart, even a month with zero sales.
2. Time intelligence functions such as TOTALYTD, SAMEPERIODLASTYEAR and DATESMTD work correctly.
3. One date table can filter many fact tables (orders, refunds, deliveries) at the same time.
4. Months sort in calendar order (Jan, Feb, Mar), not alphabetical order (Apr, Aug, Dec).

Worked example 1 (simplest): CALENDAR with fixed dates

Steps:

1. Open Power BI Desktop with the Swiggy Orders table loaded.
2. Click Modeling, then New table.
3. Type this formula and press Enter:

Date = CALENDAR ( DATE ( 2026, 1, 1 ), DATE ( 2026, 12, 31 ) )

Output: a new table called Date with one column Date and 365 rows, from 01-Jan-2026 to 31-Dec-2026.

Worked example 2: CALENDARAUTO (Power BI finds the dates)

If you do not want to type dates, use:

Date = CALENDARAUTO ()

What happens:

1. Power BI looks at every date column in your model.
2. It finds the smallest and largest date.
3. It makes full years: from 1 January of the first year to 31 December of the last year.

Output for our Swiggy data (Jan to Mar 2026): 365 rows, 01-Jan-2026 to 31-Dec-2026.

Be careful: if any table has a wrong date like a birth date of 1900, CALENDARAUTO will start from 1900. Fixed dates or the next example are safer.

Worked example 3: date range from the Orders table

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

Output: the table always covers full years of your real orders. When new orders arrive next year and you refresh, the table grows by itself.

Worked example 4: add Year, Month and Quarter columns

Select the Date table, click New column, and add each line one by one:

Year = YEAR ( 'Date'[Date] )
Month Number = MONTH ( 'Date'[Date] )
Month = FORMAT ( 'Date'[Date], "MMM" )
Quarter = "Q" & QUARTER ( 'Date'[Date] )
Weekday = FORMAT ( 'Date'[Date], "ddd" )

Output for one row:

Fix month order: click the Month column, choose Sort by column, and pick Month Number. Now Jan comes before Feb, not after Apr.

Worked example 5: the same date table in Power Query

Some teams prefer Power Query, so the table is ready before the data loads. Steps:

1. Click Home, then Transform data.
2. Click New Source, then Blank Query.
3. Open Advanced Editor and paste the code below.
4. Rename the query to Date and click Close & Apply.

let
    Start = #date(2026, 1, 1),
    End   = #date(2026, 12, 31),
    Days  = Duration.Days(End - Start) + 1,
    List  = List.Dates(Start, Days, #duration(1, 0, 0, 0)),
    Table = Table.FromList(List, Splitter.SplitByNothing(), {"Date"}),
    Typed = Table.TransformColumnTypes(Table, {{"Date", type date}}),
    Year  = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
    Month = Table.AddColumn(Year, "Month", each Date.ToText([Date], "MMM"), type text)
in
    Month

Output: the same 365-row table, with Year and Month columns.

Mark as date table and create the relationship

Steps:

1. In the Fields pane, right-click the Date table.
2. Choose Mark as date table.
3. Select the Date column and click OK. Power BI checks there are no gaps and no repeats.
4. Open Model view.
5. Drag Date[Date] onto Orders[OrderDate]. You get a one-to-many relationship (1 on Date, many on Orders).

Now build a visual: put Date[Month] on the axis and Orders[Amount] as the value. Output: Jan 750, Feb 620, Mar 200, and the other months show as empty instead of disappearing.

Use it: sales year to date

Sales YTD = TOTALYTD ( SUM ( Orders[Amount] ), 'Date'[Date] )

Output by month: Jan 750, Feb 1,370, Mar 1,570. Each month adds to the total so far. This works only because the date table has every day.

Common mistakes

1. Using Orders[OrderDate] on the axis instead of Date[Date]. Then time functions give wrong totals.
2. Forgetting the relationship. Every month then shows the same grand total.
3. OrderDate has a time part (like 05-Jan-2026 14:30). Change its data type to Date so it matches.
4. Month names sorted alphabetically. Use Sort by column with Month Number.