The request
From Brian Foster, FP&A Analyst
“I need sales by month and quarter for the forecast deck, and later year-to-date. Someone added a Date table to the model but nothing is connected to it, and the month slicer does nothing. Can you wire it up properly?”
Your goal
order_items is already related to orders. Relate orders to the Date table on the order date (many-to-one, single, active), and mark 'Date' as the model's date table using its Date column.
- Every relationship the report needs, set up rightnot done yet
- The date table markednot done yet
Your model
The model's tables: order_items (order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total), orders (order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code), Date (Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday).
Already related: order_items[order_id] > orders[order_id] | many-to-one | single | active
Loading the editor…
Reference solution
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Brian gets, and why
The lesson
Brian’s month slicer filters a table that nothing is connected to. A calendar table only works once it is related to the facts, and it works best once Power BI knows it is the calendar.
What you are working in
This is a practice diagram built for this site; Power BI Desktop runs only on Windows. Add the relationship with the form, then mark the date table. The diagram checks both the way Power BI does.
In Power BI Desktop: Model view → drag order_date from orders onto Date in the Date
table. Then select the Date table and choose Table tools → Mark as date table, pick the
Date column and press OK. Power BI validates the column before it accepts the mark.
Why a separate Date table
orders[order_date] only has the days on which someone ordered, and only the date itself. A
Date table (or calendar) has:
- one row per day, no gaps, from the first date in the data to the last (here 2023-01-01 to 2025-12-31);
- columns for every way people group time: Year, Quarter, Month, Month Number, Year Month, Weekday.
Put 'Date'[Month] on an axis and every month appears, even one with no sales. Put it on a
slicer and it filters every fact table related to the calendar, not just one.
The relationship
'Date'[Date] is unique, one row per day, so it is the “one” side. Many orders share a day, so
orders[order_date] is the “many” side:
orders[order_date] > 'Date'[Date] | many-to-one | single | activeBoth columns must be dates without a time part. Harbor & Pine’s orders store the date only; if yours carry times, change the column type to Date in Power Query first, or no time of day will ever match a calendar row.
Marking it as the date table
date table: 'Date'[Date]Marking does two things. It tells time-intelligence functions like TOTALYTD and
SAMEPERIODLASTYEAR which column holds the calendar, and it turns off Power BI’s hidden
automatic date tables for that column, which bloat the model and can’t be shared between facts.
Power BI only accepts a column of dates with no duplicates and no blanks, which is why the mark
goes on the calendar and never on a fact’s date column.
Common mistakes
- Marking the fact’s date.
orders[order_date]repeats days and has gaps. - Reversing the sides.
'Date'[Date]on the many side means “each day has many dates”; Power BI refuses it. - Slicing by
orders[order_date]. It works for one table and skips empty days. Use the'Date'columns.
Lantern lit!
Practised: date tablecalendarmark as date tablerelationshiptime intelligence
- Solved
- No hints
- First run