SAMEPERIODLASTYEAR in Power BI - Step-by-Step Guide

Introduction

SAMEPERIODLASTYEAR helps you compare data from the same time period in the previous year. It is useful when you want to see how your sales or other numbers changed year over year for the same dates.

When you want to compare this year's sales for January to last year's January sales.

When your dashboard needs to show last year's revenue for the same month currently selected.

When you want to analyze customer growth by comparing the same quarter this year and last year.

When you want to create a report showing year-over-year changes in expenses for the same date range.

When you want to highlight seasonal trends by comparing the same period last year.

Steps

Step 1: Open the Power BI Desktop file with your data model

- Power BI Desktop main window

Your report and data model are visible

Step 2: Click on the Modeling tab

- Ribbon at the top

Modeling tools are shown

Step 3: Click on New measure

- Modeling tab

A formula bar appears to enter a DAX expression

Step 4: Type the measure formula using SAMEPERIODLASTYEAR, for example: Total Sales Last Year = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Calendar'[Date]))

- Formula bar

The new measure is created and added to the Fields list

💡 Make sure you have a proper Date table marked as a date table in your model

Step 5: Add a visual like a line chart or card to your report

- Report canvas

The visual appears on the report

Step 6: Drag the new measure Total Sales Last Year to the visual's Values field

- Visualizations pane

The visual shows sales data for the same period last year

Before vs After

Before

A sales report shows total sales for the current year only, for example, $100,000 for January 2024

After

The report shows total sales for January 2023 as well, for example, $90,000, allowing easy year-over-year comparison

Settings Reference

Date Table

📍 Modeling tab > Mark as Date Table

Defines the date column used by time intelligence functions like SAMEPERIODLASTYEAR

Default: None

New Measure

📍 Modeling tab > New measure

Create calculations like SAMEPERIODLASTYEAR to compare data year over year

Default: None

Common Mistakes

Using SAMEPERIODLASTYEAR without a proper Date table marked as a date table

The function depends on a continuous date column to work correctly and will return errors or wrong results without it

Create a calendar table with continuous dates and mark it as a date table in the model before using SAMEPERIODLASTYEAR

Applying SAMEPERIODLASTYEAR on a non-date column

The function only works with date columns and will not return meaningful results on other data types

Use a date column from your calendar or date table in the SAMEPERIODLASTYEAR function

Summary

SAMEPERIODLASTYEAR lets you compare data from the same time period in the previous year.

It requires a proper date table marked as a date table in your model.

Use it in measures to create year-over-year comparisons in your reports.