How to Create a Date Table in Power BI (DAX and M)
· 7 min read
To create a date table in Power BI, add a calculated table with one row per day covering every date in your data, add the columns you want to group by (year, month, quarter), relate it to your fact table's date column, and mark it as a date table. With a Power BI date table in place, time intelligence functions such as TOTALYTD (to calculate YTD) work reliably.
Date =
ADDCOLUMNS (
CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2025, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Quarter", "Q" & QUARTER ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" )
)Then: Table tools > Mark as date table, choose the Date column, and create a relationship from 'Date'[Date] to Sales[OrderDate]. The rest of this guide explains each step, shows the Power Query version, and builds a year-to-date measure on Brightline Supply Co. data.
Why you need a date table
Power BI can create hidden date tables for you (the Auto date/time option), but it builds one per date column, they cannot be shared across fact tables, and you cannot add your own columns such as fiscal periods. A single date table you control gives you:
- One place to slice every fact table by month or quarter.
- Days with no sales still exist, so running totals and month lists have no gaps.
- Time intelligence functions (TOTALYTD, DATESYTD, SAMEPERIODLASTYEAR) that need a complete, continuous set of dates.
Once you have your own table, turn off Auto date/time in File > Options and settings > Options > Data load to keep the model small.
Calendar table in DAX
CALENDAR ( start, end ) returns a single column named Date with every day between the two dates. ADDCOLUMNS adds the attributes. Brightline's data covers all of 2025, so the range above is exact. If your data spans years, cover whole years:
Date =
VAR FirstYear = YEAR ( MIN ( Sales[OrderDate] ) )
VAR LastYear = YEAR ( MAX ( Sales[OrderDate] ) )
RETURN
ADDCOLUMNS (
CALENDAR ( DATE ( FirstYear, 1, 1 ), DATE ( LastYear, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"Quarter", "Q" & QUARTER ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmm" ),
"Year Month", FORMAT ( [Date], "yyyy-mm" )
)CALENDARAUTO () is the shortcut: it scans every date column in the model and returns full years around them. It is convenient, but a stray date (a customer signup in 2019, a placeholder like 1900-01-01) stretches the table. The explicit range is safer.
After creating the table, select the Month column and use Sort by column > Month Number, or months sort alphabetically (Apr, Aug, Dec...).
Date table in Power Query (M)
If you prefer to build tables during data load, this M query does the same in Power Query (Home > Get data > Blank query > Advanced editor):
let
StartDate = #date(2025, 1, 1),
EndDate = #date(2025, 12, 31),
DayCount = Duration.Days(EndDate - StartDate) + 1,
Dates = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
AsTable = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
Typed = Table.TransformColumnTypes(AsTable, {{"Date", type date}}),
AddYear = Table.AddColumn(Typed, "Year", each Date.Year([Date]), Int64.Type),
AddMonthNumber = Table.AddColumn(AddYear, "Month Number", each Date.Month([Date]), Int64.Type),
AddMonth = Table.AddColumn(AddMonthNumber, "Month", each Date.ToText([Date], "MMM"), type text)
in
AddMonthBoth versions give the same table. Note the different format codes: DAX uses "mmm", M uses "MMM" (in M, lowercase mm means minutes). For the general question of where a calculation belongs, see DAX vs Power Query.
Mark as date table
Select the table, then Table tools > Mark as date table and pick the Date column. Power BI checks that the column:
- has a date (or date/time) data type,
- contains unique values with no blanks,
- has no missing days between the first and last date,
- if it is date/time, uses the same time on every row.
Marking tells DAX that this column is the model's calendar. Time intelligence then removes filters on the whole date table when it shifts dates, so a measure like YTD still works when the visual is filtered by Month or Year Month rather than the Date column itself. It also removes the hidden auto date table for that column.
The example: calculate YTD
Brightline's Sales fact table has OrderDate, Status and Line Revenue, related to Products, Customers, and now Date. Six completed orders in the first quarter:
| OrderID | OrderDate | Status | Line Revenue |
|---|---|---|---|
| 50001 | 2025-01-06 | completed | 96.00 |
| 50002 | 2025-01-14 | completed | 49.50 |
| 50003 | 2025-01-30 | completed | 75.00 |
| 50004 | 2025-02-11 | completed | 112.10 |
| 50006 | 2025-03-05 | completed | 99.50 |
| 50007 | 2025-03-27 | completed | 180.00 |
Create the measures:
Revenue =
CALCULATE ( SUM ( Sales[Line Revenue] ), Sales[Status] = "completed" )
Revenue YTD =
TOTALYTD ( [Revenue], 'Date'[Date] )
Revenue YTD (DATESYTD) =
CALCULATE ( [Revenue], DATESYTD ( 'Date'[Date] ) )| Month | Revenue | Revenue YTD |
|---|---|---|
| Jan | 220.50 | 220.50 |
| Feb | 112.10 | 332.60 |
| Mar | 279.50 | 612.10 |
| Total | 612.10 | 612.10 |
TOTALYTD ( expression, dates ) and CALCULATE ( expression, DATESYTD ( dates ) ) are the same calculation. TOTALYTD is shorter. The CALCULATE form lets you combine the date filter with other filters in one place.
Fiscal years
Both functions take an optional year-end date as text. For a fiscal year ending 30 June:
Revenue FYTD =
TOTALYTD ( [Revenue], 'Date'[Date], "6-30" )YTD previous year
Compare with last year by shifting the dates:
Revenue YTD LY =
CALCULATE ( [Revenue YTD], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )On Brightline's data this returns BLANK, which is correct: the company has only 2025 data and the date table has no 2024 dates. When you try it on a real model, extend the date table to cover the prior year too.
The SQL version of a YTD column is a running total with a window function. See running totals in SQL for the comparison.
Common mistakes
Passing the fact table's date column
AI assistants often write TOTALYTD ( [Revenue], Sales[OrderDate] ). It may even return numbers. But Sales[OrderDate] has gaps (no row for days without orders) and duplicates, so DATESYTD works on an incomplete calendar, and when the visual is grouped by 'Date'[Month], the result can come back wrong or simply equal to the month's own value. How to spot it: the YTD column equals the monthly column in every row. Always pass the date table's column: 'Date'[Date].
Putting the fact table's month on the axis
The same symptom appears when the visual uses a month column from Sales (or the auto date hierarchy of Sales[OrderDate]) instead of 'Date'[Month]. Time intelligence shifts the date table's filters. Use columns from the date table on the axis.
A date/time column that does not match
If Sales[OrderDate] contains times (2025-01-06 14:32), it does not match the midnight values in the date table, and the relationship finds nothing. Change the column type to Date in Power Query.
A date table that does not cover the data
If an order is dated after the table's last day, it is lost from every visual that uses the date table. Build the range from the data, or check MAX ( Sales[OrderDate] ) against MAX ( 'Date'[Date] ).
FAQ
How do I create a date table in Power BI?
Go to Modeling > New table and enter Date = CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2025, 12, 31 ) ), wrapped in ADDCOLUMNS for year and month columns. Then mark it as a date table and relate it to your fact table.
What is the best practice for a date table?
One date table for the whole model, one row per day, whole years, no gaps, marked as a date table, with Auto date/time turned off. Hide the fact table's date columns from report view so people use the date table.
Why is my YTD measure not working?
The usual causes: the date table is not marked or not related, the measure uses the fact table's date column, the visual's axis comes from the fact table, or the date columns are date/time with times that do not match.
What is the difference between TOTALYTD and DATESYTD?
TOTALYTD returns a value (the expression evaluated year to date). DATESYTD returns a table of dates, which you use as a filter inside CALCULATE. The results are the same.
Do I need to mark as date table?
It is strongly recommended. It validates the table and makes time intelligence behave correctly no matter which date column filters the visual.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Connect the Date table” →