If you do any kind of time-based analysis in Power BI (comparing with last year, year-to-date totals, rolling averages), your model needs a separate date table. In this post I’ll explain why, and show you how to build one with DAX in a few minutes.
Why a separate date table?
- Time intelligence functions (
TOTALYTD,SAMEPERIODLASTYEAR,DATEADDand friends) expect an unbroken date column with every single day in it. Your sales table has no rows for days with no sales, so the date column in your fact table won’t do the job. - Several fact tables (sales, inventory, budget) can all be filtered through one shared date dimension.
- Columns like year, month, week and quarter are defined once and used the same way in every report.
- You no longer need the hidden auto date tables Power BI quietly creates for every date column, so the model gets smaller.
Building the date table with DAX
Go to Modeling → New table and paste the expression below. CALENDARAUTO() looks at every date column in the model and returns a range that covers full years.
Date =
ADDCOLUMNS (
CALENDARAUTO (),
"Year", YEAR ( [Date] ),
"Quarter", "Q" & QUARTER ( [Date] ),
"MonthNo", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"YearMonth", FORMAT ( [Date], "yyyy-MM" ),
"WeekNo", WEEKNUM ( [Date], 2 ),
"WeekdayNo", WEEKDAY ( [Date], 2 ),
"IsWeekend", WEEKDAY ( [Date], 2 ) >= 6
)If you’d rather set the range yourself, swap CALENDARAUTO() for CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2026, 12, 31 ) ). The 2 argument in WEEKNUM and WEEKDAY makes the week start on Monday.
Mark it as a date table
Select the table, go to Table tools → Mark as date table and pick the Date column. This is what makes time intelligence functions behave correctly. Then sort Month by MonthNo (Column tools → Sort by column), otherwise your months will show up in alphabetical order.
Create the relationship and write the measures
In Model view, create a one-to-many relationship between 'Date'[Date] and the date column in your fact table. From here on, time intelligence measures are one-liners:
Total Sales = SUM ( Sales[Amount] )
Sales YTD = TOTALYTD ( [Total Sales], 'Date'[Date] )
Sales LY = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
YoY % =
DIVIDE ( [Total Sales] - [Sales LY], [Sales LY] )One last tip
Once your date table is in place, you can turn off Auto date/time under File → Options → Current file → Data load. Power BI will stop generating a hidden table for every date column and your file will shrink. Just remember to always use the new date table for slicers and axes in your reports.
Common mistakes
- Date columns that carry a time: If the fact column is date + time (
2026-03-01 14:35), it will not match the date table and totals go missing. Change the column type to Date in Power Query. - Gaps or duplicates: To mark the table as a date table, the date column must be unique with no missing days in the range.
- Slicing by the fact table’s date: Using the sales table’s date in slicers and axes breaks time intelligence. Always use the date table.
- Ignoring the fiscal year: If your company uses a fiscal year, pass the year-end date as the last argument of
TOTALYTD, for exampleTOTALYTD([Total Sales], 'Date'[Date], "06-30").
Frequently asked questions
Should I build the date table in DAX or Power Query?
Both work. DAX is quick for a single report. If the same table will serve several reports or dataflows, Power Query (or a date dimension in your warehouse) is easier to maintain.
Why are month names in the wrong language?
FORMAT follows the model’s culture. To force a language, pass the locale argument: FORMAT([Date], "mmmm", "en-US").
Related posts
- A New Era in DAX: User-Defined Functions (UDFs)
- Direct Lake in Fabric: The Third Way Between Import and DirectQuery
- SQL Server Window Functions
STAY IN THE LOOP
New guides, sorted by topic in a single email.
Every Monday, the week's new posts arrive grouped by topic. No new posts, no email.
2 comments