How to Use Same Period Last Year in Power BI

Comparing your performance this month to the same month last year is one of the most fundamental types of business analysis. This "Same Period Last Year" (SPLY) comparison helps you understand growth, account for seasonality, and spot meaningful trends. This article walks you through exactly how to calculate and visualize Same Period Last Year metrics in Power BI using DAX.

Why Year-over-Year Analysis is So Important

Before jumping into the DAX formulas, let's quickly cover why this type of analysis is a non-negotiable for any serious report. Staring at a single number, like "we made $50,000 in sales in October," is almost meaningless on its own. Was that good? Bad? Average?

Comparing it to the previous month ($45,000 in September) adds some context, but it can be misleading. Many businesses have seasonal cycles. A retail store expects November and December to be massive compared to January and February. A tax firm is busiest in March and April. Comparing October to September doesn't tell you if you're truly growing or just riding a seasonal wave.

That’s where Year-over-Year (YoY) analysis comes in. Comparing your October sales this year to your October sales last year removes the seasonal noise. You’re comparing apples to apples. This helps you answer critical questions like:

• Are our Black Friday marketing campaigns more effective this year than last year?
• Is the summer slump we're seeing this year worse than usual?
• Is our core business actually growing, or are we just repeating last year’s patterns?

Power BI makes this analysis straightforward once you learn a few key DAX functions, but it all starts with setting up your data model correctly.

The Foundation: Your Data Model Needs a Calendar Table

Every single time intelligence calculation in DAX begins with one thing: a dedicated calendar table (often called a date table). This is the most crucial step, and skipping it is the number one reason beginners run into errors.

What is a Calendar Table and Why Do I Need One?

You might be wondering, "My sales data already has a date column. Why can't I just use that?"

DAX time intelligence functions like SAMEPERIODLASTYEAR have a specific requirement: they need a complete, uninterrupted list of dates to work with. Your sales data likely has gaps. You probably didn't have sales on Christmas Day or on certain weekends. If you tell DAX to use your sales date column, it will fail because the dates aren't contiguous.

A calendar table is a separate table in your data model that contains one row for every single day in a given period (e.g., from January 1, 2020, to December 31, 2025), without any gaps. It acts as the official source of dates for your entire report.

How to Create a Calendar Table in Power BI

Creating a basic calendar table is easy. In Power BI Desktop, go to the "Data" view, click the "Modeling" tab, and then "New Table."

Then, enter one of the following DAX formulas.

For a dynamic calendar that automatically finds the first and last date in your Sales table:

Date Table = 
CALENDAR(
    MIN(Sales[OrderDate]),
    MAX(Sales[OrderDate])
)

Note: Replace Sales[OrderDate] with the actual name of the date column in your main data table.

This creates a single-column table named "Date Table" with a column called "Date." It's a good practice to add more columns for things like Year, Month, and Quarter to make slicing your data easier.

Here’s a more robust formula you can use:

Date = 
ADDCOLUMNS (
    CALENDARAUTO(),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Quarter", "Q" & FORMAT ( [Date], "q" )
)

Set the Relationships and Mark as Date Table

Once you've created the table, you need to do two final things:

1. Create a relationship: Go to the "Model" view and drag the "Date" column from your new calendar table onto the date column in your sales table (e.g., Sales[OrderDate]). This creates a one-to-many relationship.
2. Mark as date table: Right-click on your calendar table, select "Mark as date table," and then choose the main date column from the dropdown. This tells Power BI to use this table for all time-based calculations.

With a proper calendar table in place, you're ready to write your DAX measure.

Creating Your First "Same Period Last Year" Measure

Now for the fun part. We'll use the CALCULATE and SAMEPERIODLASTYEAR functions to build our measure.

Step 1: Create a Base Measure for Your Key Metric

Before calculating last year's sales, you need a measure for this year's sales. It’s a best practice in DAX to work with measures, not raw columns dragged into visuals. Go to the "Report" view, and in the right-hand Fields pane, right-click on your Sales table and select "New measure."

Create a simple measure for total sales:

Total Sales = SUM(Sales[SalesAmount])

Step 2: Use SAMEPERIODLASTYEAR to Get a SPLY Value

Now, create another new measure. This will be our "Sales SPLY" calculation.

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

Let’s break down what this formula is doing:

• CALCULATE(...): This is a super-function in DAX. It modifies the "filter context," which just means it changes how a calculation is filtered. In this case, we're telling it to change the date filter.
• [Total Sales]: This is the expression we want to evaluate - our base sales measure from Step 1.
• SAMEPERIODLASTYEAR('Date'[Date]): This is the filter modifier that tells CALCULATE what to change. This function takes the current period (like a year, quarter, or month selected in a slicer or visual) and returns a set of corresponding dates from the previous year.

In plain English, the formula says: "Calculate the total sales, but ignore the current date filter and use the dates from the same period last year instead."

Step 3: Visualize Your New SPLY Measure

Drag a Matrix or Table visual onto your report canvas. Add your date fields from your new calendar table (e.g., Year and Month Name) into the rows. Then, add your [Total Sales] and newly created [Sales SPLY] measures to the values.

You'll now see a clear, side-by-side comparison of your sales for any given month against the same month from the previous year.

Taking it Further: Calculating Year-over-Year (YoY) Growth

Having the sales numbers side-by-side is great, but an even more powerful metric is the percentage change. Calculating Year-over-Year (YoY) growth shows you at a glance how much you've improved (or declined).

Create one more measure to calculate this percentage:

YoY Sales Growth % = 
VAR SalesLastYear = [Sales SPLY]
VAR SalesThisYear = [Total Sales]
RETURN
IF(
    NOT ISBLANK(SalesLastYear) && SalesLastYear <> 0,
    (SalesThisYear - SalesLastYear) / SalesLastYear,
    BLANK()
)

Let's unpack this a little:

• We use VAR to store the values for [Sales SPLY] and [Total Sales] in temporary variables. This makes the formula cleaner and more efficient.
• The IF statement is a safety check. It checks if there were any sales in the prior year period. If SalesLastYear is blank or zero, the formula returns blank, preventing any "divide by zero" errors that would clutter your report.
• If there were sales last year, it calculates the percentage difference: (ThisYear - LastYear) / LastYear.

After creating the measure, select it in the Fields pane and use the Measure tools in the ribbon to format it as a percentage. When you add this measure to your table, you’ll instantly see your growth rate for every period.

A More Flexible Alternative: The DATEADD Function

SAMEPERIODLASTYEAR is excellent for its specific purpose, but sometimes you need more flexibility. The DATEADD function is a fantastic alternative.

DATEADD lets you shift a date range by any interval you specify: day, month, quarter, or year.

Here’s how you’d write the SPLY measure using DATEADD:

Sales Last Year (DATEADD) = 
CALCULATE(
    [Total Sales],
    DATEADD('Date'[Date], -1, YEAR)
)

The components are:

• 'Date'[Date]: The column of dates to shift.
• -1: The number of intervals to move. A negative number moves backward in time.
• YEAR: The interval to use (other options include DAY, MONTH, QUARTER).

This formula produces the exact same result as SAMEPERIODLASTYEAR, but a major benefit of learning DATEADD is that you can easily use it for other comparisons, like Month-over-Month by simply changing the last parameter to MONTH.

Final Thoughts

Mastering time-based comparisons like "Same Period Last Year" in Power BI transforms your reports from static snapshots into dynamic analytical tools. As you've seen, it all hinges on using a proper Date Table and combining DAX functions like CALCULATE, SAMEPERIODLASTYEAR, or DATEADD to deliver powerful, context-rich insights.

While Power BI is incredibly capable, getting comfortable with data modeling and DAX can feel like a steep climb. We built Graphed because we believe valuable analysis shouldn't be locked behind complex formulas and lengthy tutorials. Instead of writing measures, you can connect your data sources in minutes and just ask a question like, "Compare our Shopify sales this month vs. the same month last year." Our AI data analyst builds the report for you instantly, turning hours of tedious work into a simple conversation.