Share of the year with a date table
Time intelligence starts with a date table: one row per day, no gaps, with columns for the year, quarter and month. The model’s Date table runs from 1 January 2024 to 31 December 2025, and Sales relates to it through Order Date.
Reports slice by those columns. To compare a quarter with its year, keep the year filter and clear the rest of the date table: ALLEXCEPT(Date, Date[Year]).
Your task
Write Share of Year: the cell’s Sales Amount divided by the Sales Amount of its whole year.
About this exercise
You practise years, quarters and allexcept on date with ALLEXCEPT and DIVIDE. The exercise runs on a small bike-shop model with a Sales fact table and Product, Customer and Date dimensions, the same star schema in every lesson.
Your measure, Share of Year, fills a Power BI-style matrix grouped by Date[Year] and Date[Quarter]. As in a real report, each cell evaluates the measure in its own filter context, and the subtotals and the grand total are evaluated again in theirs rather than added up. When you check your answer, every cell is compared with the expected value, wrong cells are marked, and the hint is about the mistake you made.
Measures that already exist in the model: Sales Amount. Reference them in square brackets, for example [Sales Amount].
Where you start
Share of Year =The result to build
| Year › Quarter | Share of Year |
|---|---|
| 2024 | 100.0% |
| Q1 | 19.5% |
| Q2 | 30.3% |
| Q3 | 27.3% |
| Q4 | 22.9% |
| 2025 | 100.0% |
| Q1 | 26.8% |
| Q2 | 19.4% |
| Q3 | 22.8% |
| Q4 | 31.1% |
| Total | 100.0% |
Last updated 10 November 2026
Opening the exercise…