Power BI year-over-year sales: a DAX measure that returns the right prior-year number
“Are we up or down versus last year?” is the first question a sales report has to answer, and it is the question most often answered with a wrong number. The usual cause is not the DAX — it is the missing calendar table. This guide builds the comparison properly: a calendar table, a relationship, and three measures. The headline result: October 2025 revenue of $13,436.42 against $10,994.74 in October 2024, a year-over-year increase of 22.21%.
Key result: In October 2025, revenue was $13,436.42, against $10,994.74 in October 2024 — YoY +22.21%. January–October 2025 totals $122,707.17 against $96,456.39 for the same months in 2024, +27.22%.
Download the data and the measures: the sales file (CSV) (66 rows, MD5 51a16fd9e023dd0208564a0c2c1a089e) and the measures file (DAX) (the three measures below, ready to paste). The data is a constructed sample — three product lines, monthly revenue from January 2024 to October 2025 — not a real company's sales.
What you need
- Power BI Desktop on Windows. Desktop is a Windows application; there is no macOS or Linux version, and no headless runner. The Desktop steps in this guide were not run on the machine that built it — see the verification note at the bottom.
- The sample file the sales file (CSV): columns
Month,Category,Units, andRevenue. One row per category per month, 66 rows. - Nothing else. No database, no service, no account.
1. Import the sales file
In Power BI Desktop, go to Home > Get data > Text/CSV, pick the sales file, and choose Load. Check the column types after loading: Month must be a date, Revenue a decimal number, and Units a whole number. If Month loads as text, the relationship in step 3 will not work — change the type in Power Query first.
2. Build the calendar table
Time-intelligence functions need a date table: a table with one row per day, continuous, with no gaps. Microsoft's documentation says to mark one of the tables containing a date column as the date table before using time-intelligence functions (Microsoft Learn: Set and use date tables in Power BI Desktop). Create it as a calculated table:
Calendar =
CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2025, 12, 31 ) )Then mark it: select the Calendar table in the Fields pane, right-click, and choose Mark as date table > Mark as date table, with Date as the date column. The same Learn page describes both the right-click path and the Table tools ribbon path. The range must cover every month in the sales file — January 2024 through October 2025 — or the prior-year lookup in step 4 returns blank for the uncovered months.
3. Relate the two tables
In Model view, drag Calendar[Date] onto Sales[Month]. The relationship is one-to-many, single direction, from Calendar to Sales. One calendar date matches many sales rows; no sales row matches more than one calendar date. If the relationship shows as many-to-many or inactive, the Month column is the wrong type — go back to step 1 and fix it.
4. Write the three measures
Create each measure on the Sales table (Modeling > New measure). The measures file (DAX) holds the same three measures, ready to paste.
Total Sales = SUM ( Sales[Revenue] )
Sales PY =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( Calendar[Date] )
)
YoY % Change =
DIVIDE (
[Total Sales] - [Sales PY],
[Sales PY]
)How it works: SAMEPERIODLASTYEAR returns the dates in the current filter context shifted one year back — Microsoft documents it as equivalent to DATEADD(dates, -1, year) (Microsoft Learn, SAMEPERIODLASTYEAR function (DAX)). CALCULATE reevaluates [Total Sales] under that shifted context, which is how the prior-year figure appears (Microsoft Learn, CALCULATE function (DAX)). DIVIDE handles the empty prior year: a month with no sales a year ago returns blank instead of an error. Microsoft's own time-intelligence training builds the same pair of measures — a prior-year measure, then a change ratio from it (Microsoft Learn, Use DAX time intelligence functions).
5. Put them in a visual
Add a clustered column chart. Put Calendar[Date] on the x-axis (use the month level of the date hierarchy), [Total Sales] and [Sales PY] as the values, and add a card visual showing [YoY % Change] formatted as a percentage with two decimals. Filter the chart to 2025 if you want the year-over-year view only; the prior-year bars still compute, because the shift happens inside the measure, not in the visual filter.
6. Check the numbers
Put Calendar[Date] (month level), [Total Sales], [Sales PY], and [YoY % Change] in a table visual. Every row must match this table, which was computed independently from the same CSV:
| Month | Total Sales ($) | Sales PY ($) | YoY % |
|---|---|---|---|
| January 2025 | 11,542.57 | 8,851.91 | +30.40% |
| February 2025 | 9,571.47 | 7,367.88 | +29.91% |
| March 2025 | 12,194.25 | 9,055.10 | +34.67% |
| April 2025 | 12,329.83 | 9,299.99 | +32.58% |
| May 2025 | 12,556.42 | 9,904.12 | +26.78% |
| June 2025 | 12,196.70 | 10,107.84 | +20.67% |
| July 2025 | 12,184.22 | 10,457.11 | +16.52% |
| August 2025 | 13,279.99 | 10,285.04 | +29.12% |
| September 2025 | 13,415.30 | 10,132.66 | +32.40% |
| October 2025 | 13,436.42 | 10,994.74 | +22.21% |
| Jan–Oct 2025 | 122,707.17 | 96,456.39 | +27.22% |
If a month shows a blank Sales PY, the calendar table is the suspect: either it was not marked as the date table, or its range does not cover the prior year. If every Sales PY equals Total Sales, the relationship is not filtering the calendar — check step 3.
Traps
TRAP — NO DATE TABLE, NO PRIOR YEAR. SAMEPERIODLASTYEAR against the sales table's own Month column returns blank, not an error, because the function shifts dates that exist in the date column — and a month-level column has no daily context to shift. The visual looks fine and the numbers are empty. Build and mark the calendar table first.
TRAP — AUTO DATE/TIME HIDES THE PROBLEM. Power BI Desktop builds hidden date tables behind every date column by default. With them on, a time-intelligence measure can appear to work while reading the hidden table instead of yours, and then behave differently once the model grows. Turn auto date/time off for the file (File > Options and settings > Options > Data Load) and rely on the marked calendar table.
TRAP — DIVIDE BY A BLANK PRIOR YEAR. Writing ([Total Sales] - [Sales PY]) / [Sales PY] with the division operator errors on months with no prior-year sales. DIVIDE returns blank instead. The first month of any new product line is exactly that case.
End state
A calendar table marked as the date table, a one-to-many relationship to sales, and three measures. The table visual shows ten months of 2025 against their 2024 equivalents, every month up between 16.52% and 34.67%, and the year to date up 27.22%. The same three measures work on any sales table with a date column and a calendar table — only the table and column names change.
Sources
- Microsoft Learn, SAMEPERIODLASTYEAR function (DAX) — shifts the dates in the current context one year back; equivalent to DATEADD(dates, -1, year)
- Microsoft Learn, CALCULATE function (DAX) — evaluates an expression in a modified filter context
- Microsoft Learn — Set and use date tables in Power BI Desktop — when a date table must be marked, and how to mark it
- Microsoft Learn, Use DAX time intelligence functions — the prior-year measure and the change-ratio measure, built the same way
Verified 10 November 2026. The measure results in the check table were computed in Python 3.12.3 with pandas 2.1.4 by grouping the same sales file by month and shifting one year back — the identical aggregation the DAX performs. October 2025: $13,436.42 vs $10,994.74, +22.21%. January–October 2025: $122,707.17 vs $96,456.39, +27.22%. The Power BI Desktop interface steps (import, calendar table, relationship, and visuals) were documented from Microsoft Learn and were not run on this machine: Power BI Desktop is Windows-only and is not installed here. Run the check table against your own report; if a row disagrees, the model differs from the one described above.