9. Lighthouse

Level 4 · Last year's sales with SAMEPERIODLASTYEAR

From Nicole Hayes, Merchandise Planner

“I'm setting 2026 stock levels month by month. For each month I need this year's sales and last year's for the same month, side by side, so I can see which months grew and which went backwards.”

Your goal

The matrix has 'Date'[Month] on its rows and a 'Date'[Year] slicer on 2025. Create a measure called Sales LY: Sales for the same period one year earlier, with CALCULATE and SAMEPERIODLASTYEAR('Date'[Date]).

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

Your measure

DAX
Visual
A matrix: rows = 'Date'[Month]; values = [Sales], [Sales LY]
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 LY] fills in when you press Check
    MonthSalesSales LY
    Jan$150,571
    Feb$99,903
    Mar$166,787
    Apr$189,734
    May$206,469
    Jun$213,062
    Jul$191,967
    Aug$182,987
    Sep$143,379
    Oct$174,599
    Nov$292,364
    Dec$288,346
    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

    “Compared with last year” is the most common comparison in business reporting. In DAX it is one CALCULATE with a function that moves the visible dates back by a year.

    What you are working in

    This page runs a practice DAX engine built for this site. It evaluates your measure for each month of 2025 and the Total, and checks every cell. In Power BI Desktop: Home → New measure, type the formula, then put 'Date'[Month] on the matrix rows (it is sorted by Month Number, so January comes first) and a 'Date'[Year] slicer on the page.

    SAMEPERIODLASTYEAR shifts the visible dates

    Sales LY = CALCULATE([Sales], SAMEPERIODLASTYEAR('Date'[Date]))

    SAMEPERIODLASTYEAR('Date'[Date]) takes the dates visible in the cell and returns each one moved back one year. For the Feb row of 2025 that is February 1–28, 2025 → February 1–28, 2024. When the last day of a month is visible, the whole shifted month is returned, so February 2025 maps to all 29 days of February 2024, the leap day included.

    CALCULATE uses the shifted dates as the filter on 'Date'[Date]. The cell still has filters on 'Date'[Month] (the row) and 'Date'[Year] (the slicer, 2025). Because the Date table is marked, the new filter on its date column replaces them all; otherwise the 2025 slicer would cut the 2024 dates away and leave a blank.

    It’s DATEADD in disguise

    SAMEPERIODLASTYEAR('Date'[Date]) is the same as DATEADD('Date'[Date], -1, YEAR). DATEADD can also shift by months or quarters, which you will use for the previous month later in this chapter.

    What the calendar needs

    • Every day present. The function returns only dates that exist in the Date table. A calendar that starts in March 2024 has no “last year” for January 2025.
    • Data in the earlier year. The model starts in 2023, so a 2023 row has nothing to compare with and Sales LY is blank there, which is correct: not zero, simply no data.

    Reading the result

    Put Sales and Sales LY side by side and the story is clear: most months of 2025 beat 2024, some by more than 40% (June). September, November and December came in slightly under last year, and November is the biggest month in both years. Nicole now knows which months to stock more boldly. The next level turns the two columns into a growth percentage.

    Common mistakes

    • Hard-coding the year. 'Date'[Year] = 2024 works for exactly one slicer setting.
    • The fact table’s date column. SAMEPERIODLASTYEAR(orders[order_date]) shifts only dates that had orders, and the gaps make some periods come out short. Use the calendar.
    • Comparing partial periods. In a live report, this year’s current month is incomplete while last year’s is whole. Compare month-to-date with month-to-date when that matters.

    Cookie settings

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