Time Intelligence
Returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context.
Table
SAMEPERIODLASTYEAR(<dates>)
SAMEPERIODLASTYEAR Function
Table of Contents
- Overview
- Syntax
- How It Works
- Business Applications
- Practical Examples
- Common Patterns
- Performance Considerations
- Common Pitfalls
- Comparison with Related Functions
- Best Practices
- 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
Parameter | Type | Required | Description |
dates | Column or Table | Yes | A column containing dates or a table expression that returns dates. Must be a continuous date range from a Date table. |
Return Value
Type: Table
Returns a single-column table containing dates from the same period in the previous year.
How It Works
Evaluation Logic
- Identifies Current Context: Examines the current filter context on the date column
- Calculates Year Shift: Moves all dates back by exactly 365 days (or 366 for leap years)
- Returns Date Table: Provides the shifted date range as a table
- 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:
Month | Sales | Sales LY |
Jan 2024 | $450,000 | $420,000 |
Feb 2024 | $480,000 | $445,000 |
Mar 2024 | $520,000 | $490,000 |
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:
Quarter | Sales | Sales LY | YoY Growth % |
Q1 2024 | $1.45M | $1.35M | 7.4% |
Q2 2024 | $1.62M | $1.48M | 9.5% |
Q3 2024 | $1.58M | $1.52M | 3.9% |
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 - LastYearCustomersBusiness Context: Tracks customer base expansion or contraction
Sample Output:
Month | Active Customers | Customers LY | Customer Growth |
Jan 2024 | 12,450 | 11,200 | +1,250 |
Feb 2024 | 12,680 | 11,380 | +1,300 |
Mar 2024 | 12,890 | 11,550 | +1,340 |
Example 4: Multi-Metric Year-over-Year Dashboard
Scenario: Create comprehensive YoY comparison measures
Sample Dashboard Output:
Metric | Current | Last Year | Change | Status |
Revenue | $2.45M | $2.18M | +12.4% | Strong Growth 📈 |
Orders | 8,450 | 7,920 | +6.7% | Moderate Growth ↗️ |
AOV | $290 | $275 | +5.5% | Moderate Growth ↗️ |
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:
Category | Sales | Sales LY | Growth % | Rank |
Electronics | $850K | $720K | +18.1% | 1 |
Clothing | $620K | $540K | +14.8% | 2 |
Home Goods | $480K | $450K | +6.7% | 3 |
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) / 3Use 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
- 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)- Minimize Context Transitions
- SAMEPERIODLASTYEAR already modifies context, avoid additional unnecessary CALCULATE layers
- 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
- 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
Dataset Size | Expected Performance | Notes |
< 1M rows | Fast (< 100ms) | Minimal impact |
1M - 10M rows | Good (< 500ms) | Ensure proper Date table |
> 10M rows | Moderate (< 2s) | May need aggregations |
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 misleadSolution:
// 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
Function | Purpose | Date Shift | Use Case |
SAMEPERIODLASTYEAR | Same period, previous year | -1 year | Standard YoY comparison |
DATEADD(..., -1, YEAR) | Flexible date shift | Variable | Custom year offsets |
PARALLELPERIOD | Parallel period shift | Variable | Quarter/month comparisons |
PREVIOUSYEAR | Entire previous year | Full year | Annual totals |
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 yearWhen 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 2024Key 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 2023Key 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 tables2. 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
- Function Behavior
- Returns a table of dates shifted back one year
- Preserves the current filter context granularity
- Requires contiguous Date table
- Evaluation Context
- How SAMEPERIODLASTYEAR modifies filter context
- Understanding the relationship with CALCULATE
- Context transition behavior
- 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
- ❌ Using transaction date column instead of Date table
- ❌ Expecting different granularity (e.g., using for fiscal year)
- ❌ Not handling blanks in denominator for growth calculations
- ❌ 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.