Date Table

Power BI · Data Modeling

Theory

A date table is a dimension with one row per day across the whole period of your data, plus columns such as Year, Quarter, Month and Month Number. It must contain every date without gaps, have a Date column of the date data type with unique values, and be marked as a date table. You relate 'Date'[Date] one-to-many to each date column in the fact tables, such as Sales[OrderDate].

Time intelligence functions like TOTALYTD and SAMEPERIODLASTYEAR rely on this continuous calendar. You can build it in DAX with CALENDAR or CALENDARAUTO, in Power Query, or import it from the warehouse. For example, CALENDAR(DATE(2024,1,1), DATE(2024,12,31)) returns 366 rows because 2024 is a leap year. Turning off the Auto date/time option is recommended once you have your own date table, so Power BI stops creating a hidden date table for every date column.

Why we use it

Without a proper date table, year-to-date, previous-year and month-level slicing are unreliable or impossible, and each date column gets its own hidden calendar that bloats the model.

When to use it

Create one for every model that analyses data over time, and use it for all date slicing. Only skip it for models with no date analysis at all.

How it works

  1. Model has date columns
  2. Create the calendar with CALENDAR or Power Query
  3. Add Year, Quarter, Month and Month Number columns
  4. Mark as date table and set Sort by column
  5. Does Sales have dates outside the calendar range? Yes: Extend the start/end dates. No: Relate 'Date'[Date] to Sales[OrderDate].
  6. Time intelligence ready

Syntax

DAX:
CALENDAR(<start_date>, <end_date>)
CALENDARAUTO([fiscal_year_end_month])
ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)
Mark: Table tools → Mark as date table → choose 'Date'[Date]
Off: File → Options and settings → Options → Data Load → Auto date/time

Example

Date = 
ADDCOLUMNS(
    CALENDAR(DATE(2024, 1, 1), DATE(2024, 12, 31)),
    "Year", YEAR([Date]),
    "Month", FORMAT([Date], "MMM"),
    "Month Number", MONTH([Date]),
    "Quarter", "Q" & QUARTER([Date])
)
// Then: Mark as date table, sort Month by Month Number,
// relate 'Date'[Date] 1 → * Sales[OrderDate]

Key points

Power BI topics

Power BI Introduction

Getting Data

Power Query (Data Transformation)

Data Modeling

DAX (Data Analysis Expressions)

Data Visualization

Dashboards & Reports

Power BI Service

Advanced Features

Projects & Case Studies

Interview & Certification

Advanced DAX & Time Intelligence

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Power BI Dashboards & DAX

Learn data visualization and interactive dashboards step-by-step. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice DAX Online 250 business scenarios on a star schema — write a measure and compare your visual with the expected one. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Power BI  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax / Where to find it
{{ current.syntax }}
Example
{{ current.example }}
What You'll Learn — click to check off
  • {{ pt.mark }} {{ pt.text }}

Share this topic