The request
From Catherine Hale, Chief Executive Officer
“For the new executive page I want three numbers across the top, and I want to trust them: sales so far this year, growth against the same period last year, and what share of what we deliver comes back. Show me how they move month by month in 2025 before they go on the cards.”
Your goal
Step 1 of 3. Create a measure called Sales YTD: the given Sales, accumulated from January 1 of the year to the end of each month. On the Total row it's the whole year.
- Fill
[Sales YTD]on every row of the visualnot done yet - The Total row right toonot done yet
Step 2 of 3. Add a measure called Sales LY (Sales for the same period one year earlier), then a measure called Sales YoY %: (Sales − Sales LY) divided by Sales LY. Sales YoY % is checked. Keep Sales YTD.
- Fill
[Sales YoY %]on every row of the visualnot done yet - The Total row right toonot done yet
Step 3 of 3. The report has a measure Delivered Lines (order lines of completed or refunded orders, the ones that reached a customer). Add a measure called Line Return Rate: the number of rows in returns divided by Delivered Lines. Keep the other measures.
- Fill
[Line Return Rate]on every row of the visualnot done yet - The Total row right toonot done yet
Step 1 · your measure
- Visual
- A matrix: rows = 'Date'[Month]; values = [Sales], [Sales YTD]
- Slicers
- 'Date'[Year] = 2025
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
Data pane: tables and columns
- 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
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| Month | Sales | Sales YTD |
|---|---|---|
| Jan | 150,570.94 | |
| Feb | 99,903.12 | |
| Mar | 166,787.31 | |
| Apr | 189,734.34 | |
| May | 206,468.94 | |
| Jun | 213,062.28 | |
| Jul | 191,966.99 | |
| Aug | 182,987.1 | |
| Sep | 143,378.62 | |
| Oct | 174,599.41 | |
| Nov | 292,364.21 | |
| Dec | 288,346.27 | |
| Total | 2,300,169.53 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 2 · your measure
- Visual
- A matrix: rows = 'Date'[Month]; values = [Sales], [Sales YoY %]
- Slicers
- 'Date'[Year] = 2025
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
Data pane: tables and columns
- 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
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| Month | Sales | Sales YoY % |
|---|---|---|
| Jan | 150,570.94 | |
| Feb | 99,903.12 | |
| Mar | 166,787.31 | |
| Apr | 189,734.34 | |
| May | 206,468.94 | |
| Jun | 213,062.28 | |
| Jul | 191,966.99 | |
| Aug | 182,987.1 | |
| Sep | 143,378.62 | |
| Oct | 174,599.41 | |
| Nov | 292,364.21 | |
| Dec | 288,346.27 | |
| Total | 2,300,169.53 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
Step 3 · your measure
- Visual
- A matrix: rows = 'Date'[Month]; values = [Sales], [Delivered Lines], [Line Return Rate]
- Slicers
- 'Date'[Year] = 2025
- Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")Delivered Lines = CALCULATE(COUNTROWS(order_items), orders[status] IN {"completed", "refunded"})
Data pane: tables and columns
- 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
- products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
- customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
- returns return_id, order_item_id, returned_on, reason, refund_amount
- Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday
Loading the editor…
Reference solution
| Month | Sales | Delivered Lines | Line Return Rate |
|---|---|---|---|
| Jan | 150,570.94 | 713 | |
| Feb | 99,903.12 | 522 | |
| Mar | 166,787.31 | 788 | |
| Apr | 189,734.34 | 767 | |
| May | 206,468.94 | 827 | |
| Jun | 213,062.28 | 856 | |
| Jul | 191,966.99 | 822 | |
| Aug | 182,987.1 | 860 | |
| Sep | 143,378.62 | 779 | |
| Oct | 174,599.41 | 864 | |
| Nov | 292,364.21 | 1,653 | |
| Dec | 288,346.27 | 1,655 | |
| Total | 2,300,169.53 | 11,106 |
Mote checks your result
Press Check. Mote fills in your answer and compares it with what the report needs.
What Catherine gets, and why
The lesson
The top band of an executive page is a few KPIs, each with a comparison. Here you build the three Catherine asked for, on a matrix by month so you can see how each one behaves before it goes on a card.
What you are working in
A practice report, not Power BI (Power BI Desktop runs only on Windows). The matrix has the months of 2025 on its rows (the year slicer is above it), and a DAX engine built for this site evaluates your measures in every cell. Each step checks the last measure in the editor; keep the earlier ones.
In Power BI Desktop, create each measure with Home → New measure, set the percentage format in Measure tools → Format, and put each KPI in a Card (or the KPI visual, with Sales LY as its target) across the top of the page. Time intelligence needs a date table marked with Table tools → Mark as date table.
Year to date
Sales YTD = TOTALYTD([Sales], 'Date'[Date])On a card filtered to the current year it shows “where we stand”. By month it rises like a staircase: $150,571 after January, $1,026,527 after June, $2,300,170 at year end.
Growth vs last year
Sales LY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
Sales YoY % = DIVIDE([Sales] - [Sales LY], [Sales LY])A KPI without a comparison is just a number. YoY growth puts it in context: 2025 is up 15.4% on 2024 overall. By month the picture is less comfortable: November and December, the two biggest months of the year with about a quarter of all sales, were slightly below 2024. That’s the kind of signal the executive page exists to surface.
Return rate
Line Return Rate = DIVIDE(COUNTROWS(returns), [Delivered Lines])returns is related to order_items, which is related to orders and the Date table, so a month
filter reaches the returns through the chain: each cell counts returns of lines ordered that
month. The denominator matters. Only completed and refunded orders reached a customer; dividing by
every line, cancelled and pending ones included, understates the rate.
Reading the KPIs together
| Sales YTD | Sales YoY % | Line Return Rate | |
|---|---|---|---|
| June | $1,026,527 | +45.2% | 6.2% |
| November | $2,011,823 | −8.4% | 6.5% |
| Year 2025 | $2,300,170 | +15.4% | 5.5% |
A strong year, a weak holiday season, and returns around one line in eighteen. The story for the page title writes itself from the measures.
Common mistakes
- YoY with
/. Months without last-year data show Infinity;DIVIDEgives BLANK. - Comparing YTD with a full last year. Compare YTD with last year’s YTD for the same dates:
CALCULATE([Sales YTD], SAMEPERIODLASTYEAR('Date'[Date])). - Trusting the latest month’s return rate. Returns arrive weeks after the order.
Lantern lit!
Practised: kpistotalytdsameperiodlastyearyoydividereturn rate
- Solved
- No hints
- First run