This post is a part of the PL-300: Microsoft Power BI Data Analyst Exam Prep Hub; and this topic falls under these sections:
Model the data (25–30%)
   --> Design and implement a data model
      --> Create a Common Date Table

Note that there are 10 practice questions (with answers and explanations) for each section to help you solidify your knowledge of the material. Also, there are 2 practice tests with 60 questions each available on the hub below the exam topics section.

A common date table (also called a calendar table) is one of the most critical components of a well-designed Power BI data model. It enables consistent time-intelligence across measures, ensures accurate filter behavior, and supports meaningful reporting.

For the PL-300: Microsoft Power BI Data Analyst exam, Microsoft expects you to understand why a common date table is needed, how to create one, and how to use it correctly in relationships and time-based calculations.

What Is a Common Date Table?

A common date table is a standalone table that contains every date (and associated date attributes) used in your fact data over the analytical time span.

It typically includes columns such as:

• Date
• Year
• Quarter
• Month
• Day
• Month Name
• Fiscal Year / Fiscal Quarter
• Week Number
• IsWeekend / IsHoliday flags

This table becomes the hub for time-intelligence calculations.

Why Use a Common Date Table?

A common date table provides:

1. Consistent Time Intelligence Across the Model

DAX time-intelligence functions (like TOTALYTD, SAMEPERIODLASTYEAR, etc.) work reliably only with a proper date table.

2. Single Point of Truth

Each date attribute (e.g., month, quarter) should come from one place — not multiple duplicated year fields across fact tables.

3. Correct Filtering

Relationships from the date table to fact tables ensure slicers and filters behave consistently.

4. Support for Multiple Date Roles

When facts have different date fields (e.g., Order Date, Ship Date), you use role-playing dimensions based on the common date table.

Where the Date Table Fits in a Power BI Model

In a star schema, the common date table acts as a dimension table connected to one or more fact tables via date fields:

         DimDate
            |
  OrderDate |--- FactSales
  ShipDate  |--- FactSales

This pattern eliminates ambiguity and supports multi-date filtering.

Creating a Common Date Table

There are several ways to create a date table in Power BI:

1. Auto Date/Time (Basic)

Power BI can automatically generate internal date tables, but this is not recommended for enterprise models or time-intelligence functions because:

• Limited control over attributes
• Cannot be customized or extended easily

For PL-300, assume you will create your own date table.

2. Using DAX (Recommended)

You can create a date table with DAX in Power BI Desktop:

Date = 
CALENDAR (
    DATE ( 2018, 1, 1 ),
    DATE ( 2025, 12, 31 )
)

You then add calculated columns:

Year = YEAR ( [Date] )
MonthNumber = MONTH ( [Date] )
MonthName = FORMAT ( [Date], "MMMM" )
Quarter = "Q" & FORMAT ( [Date], "Q" )

This gives you a fully controlled and reusable date table.

3. Using Power Query

You can also generate the date table in Power Query with List.Dates and expand to generate attributes.

Example M pattern:

let
    StartDate = #date(2018, 1, 1),
    EndDate   = #date(2025, 12, 31),
    DatesList = List.Dates(StartDate, Duration.Days(EndDate - StartDate) + 1, #duration(1,0,0,0)),
    DateTable = Table.FromList(DatesList, Splitter.SplitByNothing(), {"Date"})
in
    DateTable

Then add columns for Year, Month, Quarter, etc.

Marking a Table as a Date Table

Power BI has a special property:

Modeling → Mark as Date Table → Select the Date column

This signals to Power BI that the table is a valid date dimension. It enables full use of time-intelligence functions and prevents errors in DAX.

A valid date table:

• Must contain contiguous dates
• Must have no gaps
• Has a single unique column designated as the date

Role-Playing Dimensions for Dates

In many models, the same date table will serve multiple fact date fields, such as:

• Order Date
• Ship Date
• Promotion Date
• Invoice Date

This is typically handled by duplicating the date table (e.g., Date – Order, Date – Ship) and creating separate relationships.

Common Date Table Attributes

Here are common attributes you might include:

Exam questions may refer to building or using these attributes.

Best Practices for PL-300 Candidates

• Always create your own date table — don’t rely on auto date/time
• Mark the table as a date table in the model
• Include all relevant attributes required for slicing
• Build the table wide enough to cover all fact data ranges
• Use role-playing duplicates when necessary (e.g., Ship vs Order date)
• Name the table clearly (e.g., DimDate, DateCalendar)

How This Appears on the PL-300 Exam

Expect scenario questions like:

• Why does a time-intelligence measure return blank?
  (often because the model has no valid date table)
• How do you create a date table that supports fiscal calculations?
• Which table property enables built-in DAX functions to work correctly?
  (answer: Mark as Date Table)
• How should multiple date fields in a fact table be modeled?
  (answer: role-playing dimensions using a common date table)

The correct answers require understanding both modeling and Power BI features — not just memorizing menu locations.

Common Mistakes (Often Tested)

❌ Using a fact table’s date column as the only date source
❌ Forgetting to mark the date table as a date table
❌ Leaving gaps in the date sequence
❌ Relying solely on auto date/time
❌ Not handling multiple fact date roles properly

Key Takeaways

• A common date table is essential for reliable time-intelligence results.
• You can build a date table via DAX or Power Query.
• Always Mark as Date Table in Power BI Desktop.
• Include useful attributes for analysis (Year, Month, Quarter, etc.).
• Plan for role-playing dimensions (multiple date roles).
• This topic is heavily scenario-driven on the PL-300 exam.

Practice Questions

Go to the Practice Exam Questions for this topic.