To compare this month with the same month last year, add a Last Year Spend measure using CALCULATE with SAMEPERIODLASTYEAR over your date table, then a change measure that divides the difference by last year's figure. The pivot then shows current, prior and change side by side for every month.
What do I need first?
- A Transactions table linked to a Calendar table by date.
- The Calendar marked as a date table, covering whole years.
- A Total Spend measure: Total Spend = SUM(Transactions[Amount]).
- At least two years of data, or the previous-year column will simply be blank.
How do I write the measures?
- Last Year Spend = CALCULATE([Total Spend], SAMEPERIODLASTYEAR(Calendar[Date]))
- Change = [Total Spend] - [Last Year Spend]
- Change % = DIVIDE([Change], [Last Year Spend])
SAMEPERIODLASTYEAR moves the dates in the current pivot cell back by a year. For the April 2025 row, it points at April 2024, and CALCULATE totals those transactions. DIVIDE avoids an error when last year's figure is zero or blank.
An alternative that does the same job is DATEADD(Calendar[Date], -1, YEAR). Use it if you later want to shift by quarters or months.
What does the result look like?
With Month in Rows and the four measures in Values, and hypothetical numbers:
| Month | Spend | Last Year Spend | Change | Change % |
|---|---|---|---|---|
| Apr | 20,000 | 18,000 | 2,000 | 11.1% |
| May | 18,000 | 24,000 | -6,000 | -25.0% |
| Jun | 22,000 | 20,000 | 2,000 | 10.0% |
Check the first row: 20,000 minus 18,000 is 2,000, and 2,000 divided by 18,000 is 0.111, or 11.1%. For May, 18,000 minus 24,000 is minus 6,000, and minus 6,000 divided by 24,000 is minus 0.25, or minus 25%. Format Change % as a percentage with one decimal place.
Why is the comparison column empty or wrong?
- The first year in your data has no earlier year to compare with, so blank is correct there.
- The Calendar has gaps or is not marked as a date table. SAMEPERIODLASTYEAR needs a continuous list of dates.
- The pivot uses the Date from Transactions, not from the Calendar. Use the Calendar's Month and Year fields.
- Part-year data. If this year is only half done, Last Year Spend for those months still shows the full month last year, so compare completed months only.
Does this work with an April to March year?
Yes. SAMEPERIODLASTYEAR shifts each date back by one year, whether you group by calendar year or by financial year. If you put Financial Year in Rows, the April to March block is compared with the same block a year earlier. Just remember that the label 2025-26 will then sit beside spend for 2024-25 in the prior column.
What should I do with the comparison?
- Scan for months with a large Change % and open the category pivot for that month.
- Ignore tiny base amounts. A rise from 200 to 400 is 100%, but only 200 rupees.
- Note one-off items separately so they do not distort the picture.
Can I compare the year to date as well?
Yes. Combine the pieces: a year-to-date measure for the current year and the same measure shifted back by a year. For an April to March year, a version such as CALCULATE([Total Spend], DATESYTD(SAMEPERIODLASTYEAR(Calendar[Date]), "3/31")) gives the running total up to the matching date in the earlier year. Test it against a hand total for one month before relying on it. For a month with 20,000 this year against 18,000 last year, if the spend before it was 38,000 this year and 40,000 last year, the year-to-date pair is 58,000 against 58,000, so no change overall even though the month itself rose.
Year-on-year comparison needs consistent categories across both years. If the old data is in a messy sheet, it may be easier to import statements into a tracker such as SpendixAI, where you can set categories and see the same reports for both years; this assumes you still hold statements for the earlier year.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


