In this blog post, we show you how to create year-over-year calculations in Power BI using the built-in Data Analysis Expressions (DAX) programming language.
Year-over-year calculations are essential in business intelligence reporting. From tracking revenue, website traffic, or resource allocation, comparing current results against the same period last year gives critical context to performance trends.
Follow along as we walk you through the fundamentals of creating year-over-year (sometimes referred to as YoY or Y/Y) measures using DAX in Power BI.
What is Year-Over-Year (YoY)?
Year-over-year measures in Power BI compares performance for a metric, like sales, against the same period in the previous year. It helps answer questions like:
- “Are we doing better this March than last March?”
- “How does Q1 this year compare to Q1 last year?”
With year-over-year calculations in Power BI, you can provide your end users with necessary context.
Before You Start
While creating year-over-year DAX measures can be straightforward, they require a properly functioning Date Table. By that, this is what we mean:
- Your Date table should be set up and marked as a Date Table in Power BI
- Your Date table should include a continuous range of dates, at least covering the span of your data (preferably more)
- The Date table should be related to your fact table (i.e., sales, hours, revenue, etc.) through a single, active relationship
Power BI’s time intelligence functions require a proper Date table. If you’re using a shortcut or don’t have one, time-based calculations like YoY won’t work properly.
Step 1: Create Your Base Measure
Suppose you are analyzing sales, so you start by creating a measure for Total Sales.
Total Sales =
SUM ( ‘Fact’[Sales] )
This measure will create the total sales amount for any filter context (i.e., month, year, etc.).
Step 2: Calculate Last Year’s Sales
To calculate last year’s sales amount, we can use the base Total Sales measure by shifting the date filter context by one year. We can leverage the CALCULATE and SAMEPERIODLASTYEAR functions for this.
Total Sales Last Year =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( ‘Date Table’[Date] )
)
What this DAX calculation does is keep your current filters, but shifts your time period back by one year. For example, if your current filter context is April 2025 then this will shift the Total Sales calculation back by one year (i.e., April 2024).
Step 3: Calculate Year-Over-Year Difference
The final step is to create the year-over-year difference DAX measure. With the two measures we’ve written so far, we can create an amount or a percentage difference.
To create a year-over-year difference, use the following DAX measure:
Total Sales YoY Difference =
[Total Sales] – [Total Sales Last Year]
To create a year-over-year percentage difference, use the following DAX measure:
Total Sales YoY % Difference =
DIVIDE (
[Total Sales YoY Difference],
[Total Sales Last Year]
)
In the latter DAX measure, we use the DIVIDE function instead of the division operator (/) as it handles divisions by zero more gracefully. If, for example, last year’s sales was zero, it returns a blank value instead of an error.
[Optional] Using Variables
Creating helper DAX measures as we did in the example above is perfectly fine, but if you’re looking to do it all in one measure we can leverage variables in DAX for that. The following DAX code will create a Y/Y % Difference all in one step:
Total Sales YoY % Difference =
VAR _salesLY =
CALCULATE (
[Total Sales],
SAMEPERIODLASTYEAR ( ‘Date Table’[Date] )
)
RETURN
DIVIDE (
[Total Sales] – _salesLY,
_salesLY
)
Wrapping It Up
Year-over-year analysis is one of the most effective ways to track business performance and, with just a few DAX measures, you can integrate it directly in your Power BI reports.
For Power BI beginners, year-over-year measures are a great entry point into time intelligence functions in DAX (i.e., SAMEPERIODLASTYEAR). As you grow more comfortable, you’ll be able to expand into month-over-month, year-to-date, and even rolling averages.
Need Help Getting Started With Power BI?
Our Microsoft Certified consultants can help with the implementation of Power BI in your organization
![Read more about the article [How To] Import Data from SQL Server into Power BI](https://goanalyticsbi.com/wp-content/uploads/2023/01/Blog-Feature-Importing-data-from-SQL-Server-to-Power-BI-300x157.png)
![Read more about the article [How To] Change Data Source Location in Power Query](https://goanalyticsbi.com/wp-content/uploads/2023/07/Change-Source-Data-Location-300x157.png)
