Power BI / Intermediate: context, quality and access / Lesson 9

Date tables and time comparisons

Create an explicit calendar contract and interpret cumulative and prior-period measures with date completeness and business-calendar assumptions.

About 4 min readContent reviewed 2026-10-28

On this page

Lesson goals

Validate date-key uniqueness, coverage and relationship meaning.
Distinguish calendar-based and classic time-intelligence assumptions.
Explain year-to-date scope and incomplete-period comparisons.

Define the date dimension

A shared date table gives several facts one coherent calendar for filtering and grouping. It normally has one row per date, an unambiguous typed key, useful period attributes and a deliberate sort order. Validate unique, nonblank, contiguous dates and appropriate full-year coverage. Do not build the calendar only from dates when sales occurred, because no-sale days still belong to time. The course examples use calendar years and date-only order keys. A fiscal calendar or local-time conversion requires a separate explicit contract rather than a renamed month label on the same assumptions.

State the time-intelligence mode

Microsoft documents calendar-based time intelligence and classic time intelligence with different configuration considerations. This lesson deliberately illustrates the classic date-column approach and a properly marked Date table; it does not claim that those setup requirements apply identically to every calendar-based feature. The first snippet defines a complete2026 calendar and a year-to-date measure assuming an active Date-to-OrderDate relationship. Calculated-table and measure definitions are shown together for review but are separate model objects. No DAX engine builds or evaluates them here, and feature availability must be checked in the actual Desktop/model version.

dax Classic date-column YTD illustration

Date = CALENDAR(DATE(2026, 1, 1), DATE(2026, 12, 31))
Revenue YTD = CALCULATE([Revenue], DATESYTD('Date'[Date]))

Assume Date is marked appropriately and related to FactSales[OrderDate]. With Jan1 revenue40 and Jan2 revenue30, intended YTD at Jan2 is70. Definitions remain unexecuted.

Distinguish period and cumulative values

A daily revenue value answers what happened on that day, while a year-to-date value includes the relevant earlier dates in the year under its contract. Summing visible YTD rows would repeatedly count earlier revenue. At a total context, the measure is reevaluated rather than necessarily summing those cumulative cells. The second program exposes daily30 versus cumulative70 for the second synthetic day. It is an independent calendar arithmetic model and does not reproduce DATESYTD. Preserve these expected values before adding prior-year comparisons or complex fiscal rules.

python Daily versus cumulative oracle

from datetime import date
revenue = {date(2026, 1, 1): 40, date(2026, 1, 2): 30}
cutoff = date(2026, 1, 2)
print(revenue[cutoff])
print(sum(value for day, value in revenue.items() if day.year == cutoff.year and day <= cutoff))

Expected output

30
70

30 is the selected day and70 the modeled year-to-date amount. No date table or DAX function is executed.

Compare like periods

A current month containing only two loaded days is not automatically comparable with a complete prior month or year. Define whether the comparison uses matched elapsed days, complete periods or another approved cutoff. Refresh freshness and late-arriving facts can change the apparent trend. A date slicer does not explain these policies by itself. Also distinguish order date from shipment date: the same transaction can belong to different periods depending on the question. Name the role in measures and verify a transaction whose two dates differ so a relationship choice cannot silently change the business meaning.

Review calendar edge cases

Include a no-sale date, year boundary, leap-year case where relevant, incomplete period and two date roles. Confirm the calendar’s range covers both facts and requested comparisons. Formatting a timestamp as a date does not necessarily remove its time component for relationship matching; prepare the intended key. This course runs only the two-day oracle and makes no claim about actual model marking, engine behavior or service freshness. A useful review record states the calendar system, date role, mode assumptions, completeness cutoff and expected cumulative result for each test before the report is distributed.

Common errors and fixes

The date table excludes days without sales

Why it happens: Fact occurrence dates replaced the calendar completeness contract.
How to fix it: Use a complete supported calendar range and validate missing dates.
Prevent it next time: Keep a no-sale-date fixture.

Visible YTD values are summed as independent daily amounts

Why it happens: Cumulative overlap was ignored.
How to fix it: Evaluate the intended total context and explain repeated inclusion.
Prevent it next time: Compare daily and cumulative expected results side by side.

An incomplete current period appears to collapse against a full prior period

Why it happens: Period completeness and data cutoff were unspecified.
How to fix it: Apply the approved like-period comparison rule and expose freshness.
Prevent it next time: Document cutoff policy with the metric.

Check your understanding

1. Why should a no-sale day exist in Date even if FactSales has no row for it?

Show worked solution

The calendar represents time, not just observed transactions. Its continuity supports grouping and defined time-intelligence behavior. The fact absence can then remain a meaningful no-event case. Build and validate the complete date dimension under the chosen mode instead of deriving its completeness from sales activity.

2. At Jan2 daily revenue is30 and YTD70. Why is adding Jan1 YTD40 to Jan2 YTD70 misleading?