SAMEPERIODLASTYEAR Function

Table of Contents

1. Overview
2. Syntax
3. How It Works
4. Business Applications
5. Practical Examples
6. Common Patterns
7. Performance Considerations
8. Common Pitfalls
9. Comparison with Related Functions
10. Best Practices
11. PL-300 Exam Focus

Overview

SAMEPERIODLASTYEAR is a time intelligence function that returns a table containing dates from the same period in the previous year. It automatically shifts the current filter context back by exactly one year, making it essential for year-over-year (YoY) comparisons in business analytics.

Category: Time Intelligence - Period Comparison

Primary Use Cases:

• Year-over-year sales comparisons
• Annual growth calculations
• Historical performance analysis
• Seasonal trend identification

Key Characteristics:

• Returns a table of dates (used within iterator functions)
• Automatically handles leap years
• Works with continuous date ranges
• Requires a proper Date table with contiguous dates

Syntax

SAMEPERIODLASTYEAR(<dates>)

Parameters

Return Value

Type: Table

Returns a single-column table containing dates from the same period in the previous year.

How It Works

Evaluation Logic

1. Identifies Current Context: Examines the current filter context on the date column
2. Calculates Year Shift: Moves all dates back by exactly 365 days (or 366 for leap years)
3. Returns Date Table: Provides the shifted date range as a table
4. Applies to Measures: Used within measure calculations to evaluate metrics in the prior year context

Context Behavior

// Current filter: January 2024
SAMEPERIODLASTYEAR('Date'[Date])
// Returns: January 2023

// Current filter: Q2 2024 (Apr-Jun)
SAMEPERIODLASTYEAR('Date'[Date])
// Returns: Q2 2023 (Apr-Jun)

Equivalent Expression

SAMEPERIODLASTYEAR('Date'[Date])

// Is equivalent to:
DATEADD('Date'[Date], -1, YEAR)

Business Applications

1. Retail & E-commerce

• Holiday Season Comparison: Compare Christmas sales 2024 vs 2023
• Back-to-School Performance: Track year-over-year August revenue
• Promotional Effectiveness: Evaluate same-period promotions annually

2. Financial Services

• Annual Revenue Growth: Calculate YoY revenue changes
• Investment Performance: Compare portfolio returns year-over-year
• Credit Risk Analysis: Track loan default rates annually

3. Manufacturing

• Production Volume Trends: Monitor year-over-year output
• Quality Metrics: Compare defect rates to previous year
• Inventory Turnover: Analyze seasonal inventory patterns

4. Healthcare

• Patient Volume Analysis: Compare admission rates YoY
• Treatment Outcomes: Track annual improvement in patient outcomes
• Resource Utilization: Monitor equipment usage year-over-year

Practical Examples

Example 1: Basic Year-over-Year Sales

Scenario: Calculate total sales for the same period last year

Dataset: Contoso Sales

Sales LY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

Business Context:

• When viewing January 2024 sales: shows January 2023 sales
• When viewing Q3 2024: shows Q3 2023 sales
• Automatically adjusts to any date granularity in the visual

Sample Output:

Example 2: Year-over-Year Growth Percentage

Scenario: Calculate the percentage change from last year

YoY Growth % =
VAR CurrentSales = SUM(Sales[SalesAmount])
VAR PreviousYearSales =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )
RETURN
    DIVIDE(
        CurrentSales - PreviousYearSales,
        PreviousYearSales,
        0
    )

Business Context:

• Positive values indicate growth
• Negative values indicate decline
• DIVIDE handles zero/blank scenarios safely

Sample Output:

Example 3: Year-over-Year Customer Count

Scenario: Compare active customer counts year-over-year

Dataset: AdventureWorksDW

Customers LY =
CALCULATE(
    DISTINCTCOUNT(Sales[CustomerKey]),
    SAMEPERIODLASTYEAR('Date'[DateKey])
)

Customer Growth =
VAR CurrentCustomers = DISTINCTCOUNT(Sales[CustomerKey])
VAR LastYearCustomers = [Customers LY]
RETURN
    CurrentCustomers - LastYearCustomers

Business Context: Tracks customer base expansion or contraction

Sample Output:

Example 4: Multi-Metric Year-over-Year Dashboard

Scenario: Create comprehensive YoY comparison measures

Sample Dashboard Output:

Example 5: Seasonal Pattern Analysis

Scenario: Identify seasonal trends by comparing same months across years

Holiday Sales Index =
VAR CurrentMonthSales = SUM(Sales[SalesAmount])
VAR LastYearMonthSales =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )
VAR AnnualAvgSales =
    CALCULATE(
        AVERAGE(Sales[SalesAmount]),
        ALL('Date'),
        'Date'[Year] = MAX('Date'[Year])
    )
RETURN
    DIVIDE(CurrentMonthSales, AnnualAvgSales, 0)

Business Context: Values > 1.0 indicate above-average performance for seasonal peaks

Example 6: Product Category YoY Performance

Scenario: Compare product category performance year-over-year

Category Sales LY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

Category Growth Rank =
VAR GrowthRate =
    DIVIDE(
        [Revenue] - [Category Sales LY],
        [Category Sales LY],
        0
    )
RETURN
    RANKX(
        ALL(Products[Category]),
        GrowthRate,
        ,
        DESC,
        DENSE
    )

Sample Output:

Common Patterns

Pattern 1: YoY Variance with Conditional Formatting

YoY Variance = [Revenue] - [Revenue LY]

YoY Variance Color =
SWITCH(
    TRUE(),
    [YoY Variance] > 0, "Green",
    [YoY Variance] < 0, "Red",
    "Gray"
)

Use Case: Dynamic visual formatting based on performance

Pattern 2: Multiple Year Comparison

Sales 2 Years Ago =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR(
        SAMEPERIODLASTYEAR('Date'[Date])
    )
)

3-Year Trend =
VAR Current = [Revenue]
VAR LY = [Revenue LY]
VAR TwoYearsAgo = [Sales 2 Years Ago]
RETURN
    (Current + LY + TwoYearsAgo) / 3

Use Case: Long-term trend analysis and smoothing

Pattern 3: YoY with Filter Override

Total YoY Growth =
VAR CurrentTotal =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        ALL(Products[Category])
    )
VAR LastYearTotal =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        SAMEPERIODLASTYEAR('Date'[Date]),
        ALL(Products[Category])
    )
RETURN
    DIVIDE(CurrentTotal - LastYearTotal, LastYearTotal, 0)

Use Case: Company-wide growth regardless of category filters

Pattern 4: Conditional YoY (Only Calculate if Data Exists)

Safe YoY Growth =
VAR CurrentSales = SUM(Sales[SalesAmount])
VAR PreviousSales =
    CALCULATE(
        SUM(Sales[SalesAmount]),
        SAMEPERIODLASTYEAR('Date'[Date])
    )
RETURN
    IF(
        NOT ISBLANK(PreviousSales),
        DIVIDE(CurrentSales - PreviousSales, PreviousSales, 0),
        BLANK()
    )

Use Case: Avoid misleading growth calculations for new products/periods

Performance Considerations

Optimization Tips

1. Use Variables for Repeated Calculations

// ❌ Less efficient
YoY Growth =
DIVIDE(
    [Revenue] - CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date])),
    CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date])),
    0
)

// ✅ More efficient
YoY Growth =
VAR LY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN DIVIDE([Revenue] - LY, LY, 0)

1. Minimize Context Transitions

• SAMEPERIODLASTYEAR already modifies context, avoid additional unnecessary CALCULATE layers

1. Date Table Requirements

• Ensure Date table has contiguous dates without gaps
• Mark as Date table in Power BI for optimization
• Include all necessary date hierarchies

1. Avoid Row Context in Large Tables

// ❌ Avoid if possible
SUMX(
    Sales,
    VAR LY = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
    RETURN [Revenue] - LY
)

// ✅ Calculate at measure level
[Revenue] - [Revenue LY]

Performance Impact

Common Pitfalls

Pitfall 1: Missing or Incomplete Date Table

Problem:

// Using transaction dates directly
Sales LY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR(Sales[OrderDate]) // ❌ May not work properly
)

Solution:

// Use proper Date table
Sales LY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date]) // ✅ Correct
)

Why: Transaction dates may have gaps; Date table ensures continuity

Pitfall 2: Leap Year Confusion

Problem: Expecting February 29, 2024 to map to February 29, 2023 (which doesn't exist)

Solution: SAMEPERIODLASTYEAR handles this automatically by adjusting to February 28, 2023

// No special handling needed - DAX handles leap years
Sales LY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

Pitfall 3: Incorrect Blank Handling

Problem:

// Shows 0% for periods with no last year data
YoY Growth =
DIVIDE([Revenue] - [Revenue LY], [Revenue LY], 0) // ❌ May mislead

Solution:

// Show blank when no comparison data exists
YoY Growth =
IF(
    NOT ISBLANK([Revenue LY]),
    DIVIDE([Revenue] - [Revenue LY], [Revenue LY], 0),
    BLANK()
)

Pitfall 4: Mixing Fiscal and Calendar Years

Problem: Using SAMEPERIODLASTYEAR with fiscal year expectations

Solution:

// For fiscal years, use DATEADD with custom logic
Sales Last Fiscal Year =
CALCULATE(
    SUM(Sales[SalesAmount]),
    DATEADD('Date'[Date], -1, YEAR),
    'Date'[FiscalYear] = MAX('Date'[FiscalYear]) - 1
)

Comparison with Related Functions

Detailed Comparison

SAMEPERIODLASTYEAR vs DATEADD

// These are equivalent:
SAMEPERIODLASTYEAR('Date'[Date])
DATEADD('Date'[Date], -1, YEAR)

// DATEADD is more flexible:
DATEADD('Date'[Date], -2, YEAR)  // 2 years ago
DATEADD('Date'[Date], 1, YEAR)   // Next year

When to use SAMEPERIODLASTYEAR: Standard year-over-year comparisons (more readable)

When to use DATEADD: Need flexibility for different year offsets

SAMEPERIODLASTYEAR vs PARALLELPERIOD

// SAMEPERIODLASTYEAR - matches current period exactly
Sales LY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)
// Jan 2024 → Jan 2023
// Q1 2024 → Q1 2023

// PARALLELPERIOD - shifts by specified intervals
Sales Previous Quarter =
CALCULATE(
    SUM(Sales[SalesAmount]),
    PARALLELPERIOD('Date'[Date], -1, QUARTER)
)
// Q2 2024 → Q1 2024

Key Difference: SAMEPERIODLASTYEAR always goes back one year; PARALLELPERIOD can shift by months/quarters/years

SAMEPERIODLASTYEAR vs PREVIOUSYEAR

// SAMEPERIODLASTYEAR - matches current filter
Sales SPLY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)
// Viewing Jan 2024 → Shows Jan 2023

// PREVIOUSYEAR - always returns entire year
Sales PY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    PREVIOUSYEAR('Date'[Date])
)
// Viewing Jan 2024 → Shows all of 2023

Key Difference: SAMEPERIODLASTYEAR preserves the time granularity; PREVIOUSYEAR always returns the full year

Best Practices

1. Always Use a Proper Date Table

// ✅ Correct approach
Date =
CALENDAR(
    DATE(2020, 1, 1),
    DATE(2025, 12, 31)
)

// Mark as Date table in Power BI
// Ensure relationship to fact tables

2. Use Variables for Clarity and Performance

3. Handle Edge Cases Gracefully

// ✅ Robust implementation
Safe YoY Growth =
VAR Current = [Revenue]
VAR LastYear = CALCULATE([Revenue], SAMEPERIODLASTYEAR('Date'[Date]))
RETURN
    SWITCH(
        TRUE(),
        ISBLANK(LastYear), BLANK(),
        LastYear = 0, BLANK(),
        DIVIDE(Current - LastYear, LastYear, 0)
    )

4. Create Base Measures for Reusability

// Base measure
_Sales LY =
CALCULATE(
    SUM(Sales[SalesAmount]),
    SAMEPERIODLASTYEAR('Date'[Date])
)

// Derived measures
YoY Growth = [Revenue] - [_Sales LY]
YoY Growth % = DIVIDE([Revenue] - [_Sales LY], [_Sales LY], 0)

5. Document Business Logic

PL-300 Exam Focus

Key Concepts to Master

1. Function Behavior
• Returns a table of dates shifted back one year
• Preserves the current filter context granularity
• Requires contiguous Date table
2. Evaluation Context
• How SAMEPERIODLASTYEAR modifies filter context
• Understanding the relationship with CALCULATE
• Context transition behavior
3. Common Scenarios
• Year-over-year sales comparison
• Growth percentage calculations
• Multi-year trend analysis

Exam-Style Questions

Question 1: You need to create a measure that shows sales for the same month last year. The current filter shows "March 2024". Which function would you use?

Answer: SAMEPERIODLASTYEAR - returns March 2023 data automatically

Question 2: What is the difference between SAMEPERIODLASTYEAR and PREVIOUSYEAR?

Answer:

• SAMEPERIODLASTYEAR: Returns same period as current context (e.g., Jan → Jan)
• PREVIOUSYEAR: Returns entire previous year regardless of current filter

Question 3: Your SAMEPERIODLASTYEAR measure returns blank for all rows. What is the most likely cause?

Answer: The Date table is not properly configured with continuous dates, or the relationship between Date and fact tables is missing/incorrect

Formula Patterns for Exam

Pattern 1: Basic YoY

Measure LY = CALCULATE([Measure], SAMEPERIODLASTYEAR('Date'[Date]))

Pattern 2: YoY Growth

YoY % = DIVIDE([Measure] - [Measure LY], [Measure LY], 0)

Pattern 3: Conditional YoY

Safe YoY = IF(NOT ISBLANK([Measure LY]), [Measure] - [Measure LY], BLANK())

Common Exam Traps

1. ❌ Using transaction date column instead of Date table
2. ❌ Expecting different granularity (e.g., using for fiscal year)
3. ❌ Not handling blanks in denominator for growth calculations
4. ❌ Confusing with PREVIOUSYEAR (which returns full year)

Quick Reference Card

Summary

SAMEPERIODLASTYEAR is an essential time intelligence function for year-over-year analysis, automatically shifting the current date context back by one year. It's the go-to function for YoY comparisons, growth calculations, and trend analysis.

Key Takeaways:

• ✅ Use for standard year-over-year comparisons
• ✅ Requires proper Date table with continuous dates
• ✅ Preserves current filter context granularity
• ✅ Handles leap years automatically
• ✅ Combine with CALCULATE for measure evaluations
• ✅ Use variables to avoid redundant calculations

Remember: SAMEPERIODLASTYEAR returns a table of dates, so it must be used within CALCULATE or other functions that accept table parameters.