9. Lighthouse

Level 5 · Year-over-year growth with VAR and DIVIDE

From Rachel Kim, Category Manager

“Leadership wants one number per category for the annual review: how much 2025 sales grew over 2024, as a percentage. Positive means growth, negative means decline. The page has a Year slicer on 2025.”

Your goal

The matrix has products[category] on its rows and a 'Date'[Year] slicer on 2025. Create a measure called Sales YoY %: (Sales − last year's Sales) ÷ last year's Sales. Store last year's Sales (CALCULATE with SAMEPERIODLASTYEAR) in a variable and divide with DIVIDE.

  • Fill [Sales YoY %] on every row of the visual
  • The Total row right too

Your measure

DAX
Visual
A matrix: rows = products[category]; 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…

    Visual[Sales YoY %] fills in when you press Check
    categorySalesSales YoY %
    Decor$290,301
    Furniture$573,131
    Kitchen$292,808
    Lighting$385,145
    Outdoor$507,358
    Textiles$251,426
    Total$2,300,170

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    A growth rate answers “by how much did it change, relative to where it started”. Year over year (YoY), that is:

    growth = (this year − last year) ÷ last year

    In DAX, last year’s figure is a CALCULATE with SAMEPERIODLASTYEAR, as in the previous level. This level adds two habits that make measures like this readable and safe: variables and DIVIDE.

    What you are working in

    This page runs a practice DAX engine built for this site. It evaluates your measure for each category in 2025 and the Total row, and checks every cell. In Power BI Desktop: Home → New measure, type the formula (Shift+Enter for new lines), and set Measure tools → Format → Percentage.

    VAR … RETURN

    Sales YoY % =
VAR SalesLY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    DIVIDE([Sales] - SalesLY, SalesLY)
    • VAR name = expression computes a value once and gives it a name. Variables are evaluated in the filter context where they are defined: here the cell’s own context, so SalesLY is last year’s sales for this category.
    • RETURN gives the measure’s result, using the variables.

    Without a variable you would write the CALCULATE twice, once in the numerator and once in the denominator. That is harder to read, easier to get out of step when you edit one copy, and (in large models) slower. Variables also make debugging easy: temporarily write RETURN SalesLY to see the intermediate value in the matrix.

    DIVIDE instead of /

    DIVIDE(numerator, denominator) returns a blank when the denominator is zero or blank. A category launched this year has no sales last year; / would produce an error or infinity in that cell, DIVIDE leaves it empty. An optional third argument sets what to return instead, DIVIDE(a, b, 0).

    Reading the result

    Category 2025 vs 2024
    Textiles +29.3%
    Outdoor +25.5%
    Lighting +18.3%
    Furniture +12.7%
    Decor +4.1%
    Kitchen +4.0%

    Store-wide, 2025 sales were 15.4% above 2024. Growth rates make categories of very different size comparable: Furniture added the most dollars, but Textiles grew fastest.

    Common mistakes

    • The ratio instead of the growth. DIVIDE([Sales], SalesLY) gives 1.183 for Lighting. Growth is that minus 1.
    • Variables don’t change context. VAR s = [Sales] RETURN CALCULATE(s, …) doesn’t re-evaluate s under the new filters: a variable is a fixed value once computed. Put the CALCULATE inside the VAR, as here.
    • Percent points vs percent. Going from 10% to 12% margin is +2 percentage points, but +20% growth. Say which one you mean.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.