How to Use SAMEPERIODLASTYEAR in Power BI

Performing a year-over-year (YoY) analysis is fundamental for understanding business performance, but getting it right in Power BI can feel intimidating. If you've been manually filtering dates or struggling with complex formulas, the SAMEPERIODLASTYEAR function is what you need. This straightforward DAX function unlocks powerful time-based comparisons. This guide will walk you through exactly how to use it, from setting up your data model to building practical reports.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Is SAMEPERIODLASTYEAR, and Why Should You Care?

In simple terms, SAMEPERIODLASTYEAR is a time intelligence function in DAX (Data Analysis Expressions) that returns a table of dates from the previous year that corresponds to the current date context. Think of it as a time machine for your data. If your report is filtered to show May 2024, using this function in a measure will calculate results based on the data from May 2023.

Why is this so important? Because it automates one of the most common and critical business analytics tasks:

• Sales Reporting: Are sales up or down compared to this time last year?
• Marketing Analysis: How many leads did we generate this quarter versus the same quarter last year?
• Website Analytics: Is our site traffic growing year-over-year?

Without SAMEPERIODLASTYEAR, you'd be stuck building manual filters or unwieldy measures that are difficult to update and prone to error. This function lets you build dynamic, accurate YoY reports with very little effort.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Before You Start: Your Data Model Essentials

Time intelligence functions in DAX have one non-negotiable requirement: a proper Date Table. Using functions like SAMEPERIODLASTYEAR without one will lead to errors or blank results. Before you write a single line of DAX, make sure your data model is correctly set up.

The All-Important Date Table

A Date Table (sometimes called a calendar table) is a separate table in your Power BI model dedicated exclusively to dates. It must contain a continuous sequence of dates covering the entire period of your data - no gaps allowed.

For your Date Table to work with time intelligence functions, it must have:

1. A Date Column: This column must have the 'Date' data type and contain one unique value for every single day in your desired date range (e.g., from January 1, 2020, to December 31, 2025).
2. A Relationship to Your Fact Tables: You must create a one-to-many relationship from your Date Table's primary date column to the date column in your fact tables (e.g., your sales table, marketing leads table, etc.). For example, you would connect 'Date'[Date] to 'Sales'[OrderDate].

After creating it, you must officially designate it in Power BI. Go to the Table view, select your Date Table, click on the 'Table tools' ribbon, and select 'Mark as date table.' This tells Power BI which table to use for its internal time-based calculations.

Step-by-Step Guide: Creating Your First YoY Measure

Once your Date Table is in place, creating a YoY measure is a straightforward three-step process. Let's imagine we want to compare current sales to last year's sales.

Our data model contains a Sales table with an Amount column and an OrderDate column, and our Date table is set up correctly.

Step 1: Create a Base Measure

First, we need a simple base measure to calculate the sum of sales. This gives us something to reference in our next step.

On the report canvas view, right-click on your Sales table and select "New measure." In the formula bar, enter:

Total Sales = SUM(Sales[Amount])

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Create the SAMEPERIODLASTYEAR Measure

Now we'll create the measure that calculates sales for the previous year. Right-click the Sales table again and click "New measure."

Sales Last Year = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Date'[Date]))

Let's break down this formula:

• CALCULATE(): This is the most important function in DAX. It modifies the existing filter context. We're telling it to calculate something, but with a different set of filters.
• [Total Sales]: This is the expression we want to evaluate. We're asking CALCULATE to compute our base measure.
• SAMEPERIODLASTYEAR('Date'[Date]): This is our new filter. It tells CALCULATE to ignore the currently selected dates and instead use the corresponding dates from the prior year. Notice we use the date column from our Date table, not the Sales table.

Step 3: Build Your Visualization

Drag a Matrix visual onto your Power BI report canvas. Now let's add the data:

• To the Rows, add Year and Month from your Date table.
• To the Values, add your [Total Sales] and [Sales Last Year] measures.

You’ll immediately see your sales figures for the current period next to the sales figures for the exact same period one year prior, letting you easily spot trends.

Beyond the Basics: Practical Examples

Once you've mastered the basic structure, you can apply it to all sorts of metrics and even build on it to calculate growth rates.

Calculating Year-over-Year Growth Percentage

The next logical step is to calculate the YoY growth rate. This requires your two existing measures: Total Sales and Sales Last Year.

Create a new measure:

YoY Sales Growth % = 
DIVIDE(
    [Total Sales] - [Sales Last Year], 
    [Sales Last Year]
)

You can then format this measure as a percentage. Using DIVIDE() is safer than the standard slash (/) operator because it automatically handles division-by-zero errors. If Sales Last Year is blank or zero, DIVIDE() will return a blank instead of an error.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Comparing More Than Just Sales

The pattern is adaptable to almost any metric. Just swap out the base measure.

To compare website traffic from Google Analytics:

Total Sessions = SUM('Google Analytics'[Sessions])
Sessions Last Year = CALCULATE([Total Sessions], SAMEPERIODLASTYEAR('Date'[Date]))

To compare leads from HubSpot:

Total Leads = COUNT(HubSpot[ContactID])
Leads Last Year = CALCULATE([Total Leads], SAMEPERIODLASTYEAR('Date'[Date]))

Common Traps and How to Avoid Them

SAMEPERIODLASTYEAR is powerful but can be finicky if you miss a step. Here are the most common issues users face.

• “My Measure Returns Blank.” This is the number one issue. It almost always comes down to one of two things:
• “I Used My Sales Table's Date Column.” You must use the date column from your dedicated Date table inside the SAMEPERIODLASTYEAR function. These time intelligence functions are designed to operate on a continuous, clean calendar, which your transactional table (like a Sales table) cannot guarantee.
• Forgetting About the Context. Remember that the "period" in SAMEPERIODLASTYEAR is determined by the filters active in your report. If you filter your chart to show a specific quarter, the function will return the same quarter from last year. If you filter by a single day, it returns that single day from last year. It’s dynamic!

Final Thoughts

The SAMEPERIODLASTYEAR function is a core component of any serious reporting in Power BI. By pairing it with a proper Date Table and the CALCULATE function, you can replace clunky manual comparisons with dynamic, repeatable year-over-year measures for any part of your business.

While mastering DAX can be deeply rewarding, it requires a significant amount of setup and learning just to start answering fundamental business questions. At Graphed, we created a single platform where you can connect your data sources - like Google Analytics, Shopify, and Salesforce - and an AI data analyst automates this for you. Instead of writing formulas, you simply ask in plain English, "Show me a chart comparing sales this month vs. last month vs. this month last year," and the report is instantly created for you. It's all about helping you spend less time wrangling data and more time acting on the insights.