9. Lighthouse

Level 8 · Fix a measure with VAR and RETURN

From Ryan Patel, Senior BI Developer

“Our Sales YoY % repeats last year's sales twice, and someone 'fixed' one copy to DATEADD by a month. Now Textiles shows +22% when it grew about 29%. Please rewrite it so last year's sales is written exactly once, with a name, and the numbers are right again.”

Your goal

The matrix has products[category] on its rows with a slicer on 'Date'[Year] = 2025. The editor holds the broken measure. Rewrite Sales YoY % as ([Sales] − last year's sales) / last year's sales, where last year's sales is CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date])). Store last year's sales in a VAR and use it in RETURN.

  • 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

    The old measure isn’t wrong because the maths is hard. It’s wrong because the same idea, “last year’s sales”, was written twice, and two copies of one idea drift apart. Variables fix that by giving the idea a name.

    What you are working in

    This is a practice report built for this site, not Power BI: Power BI Desktop runs only on Windows. A DAX engine on the page evaluates your measure in each cell, following Power BI’s rules. In Power BI Desktop, select the measure in the Data pane and edit it in the formula bar; Shift+Enter adds a new line. The engine here checks the numbers, so make sure the VAR version is also the correct one.

    VAR … RETURN

    Sales YoY % =
VAR SalesLY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    DIVIDE([Sales] - SalesLY, SalesLY)
    • VAR name = expression evaluates the expression and stores the result. You can define several variables, each on its own line, and later ones can use earlier ones.
    • RETURN expression is the value of the measure. It can use every variable above it.
    • Names have no spaces or brackets: SalesLY, _lastYear. Variables are local to the measure; nothing outside it can see them.

    Why it’s more correct, not only tidier

    1. One definition. Last year’s sales exists once. Change it to a fiscal year or a different date column, and both uses follow.
    2. Evaluated once. The engine computes SalesLY one time per cell instead of twice. On big models that is real speed.
    3. Evaluated where it’s defined. A variable holds a value, taken in the context of the line where it is written. Wrapping it in CALCULATE later doesn’t recompute it. That’s what you want here, and it surprises people who expect it to behave like a measure.

    Reading the old bug

    The numerator used SAMEPERIODLASTYEAR: 2025 compared with 2024. The denominator used DATEADD by one month: December 2024 to November 2025. For Textiles, the growth was divided by the wrong base, so +29.3% became +22.2%. Nothing errors, every number looks plausible, and the measure is still wrong: the most expensive kind of bug in a report.

    VAR for debugging

    When a long measure gives a strange result, put each step in a VAR and change the RETURN line to show one of them: RETURN SalesLY. Check that number in the matrix, then move on to the next variable. Ryan’s team now writes every ratio this way: numerator, denominator, RETURN DIVIDE.

    Common mistakes

    • Spaces in the name. VAR Sales LY is a syntax error.
    • Forgetting RETURN. A measure with only VAR lines has no value.
    • A variable after RETURN. Every VAR line comes before the RETURN that uses it.

    Cookie settings

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