Historical Context & Motivation
Comparing current performance against past performance is one of the most fundamental operations in business analytics. Long before modern BI tools existed, accountants and analysts manually compiled prior period comparisons by pulling figures from ledgers, spreadsheets, or databases and placing them side by side — a tedious, error-prone process that nonetheless delivered enormous insight into whether a business was improving, stagnating, or declining. The evolution from manual spreadsheet formulas to declarative, model-aware functions like those in DAX (Data Analysis Expressions) represents a paradigm shift: instead of writing imperative date-arithmetic logic, an analyst declares what time shift to apply and lets the engine resolve the filter context.
The central question these functions address is deceptively simple: given a measure evaluated under a particular date filter, what would that measure have been for the equivalent period one year — or N intervals — ago? Answering this requires manipulating filter context over a properly structured date table, which is exactly what SAMEPERIODLASTYEAR and DATEADD were designed to do.
Core Principles & Definitions
Before diving into the syntax of individual functions, it is essential to understand the foundational mechanics that all DAX time-intelligence functions share. These functions do not simply subtract days from date columns; they operate by returning modified date tables that replace the current filter context's date range with a shifted equivalent. The engine then re-evaluates the target measure under this new filter context, producing the prior-period value. This architecture mirrors the functional-programming concept of higher-order functions: a time-intelligence function takes a date set as input, transforms it, and returns a new date set that CALCULATE uses to override the original filter.
Date Table Requirement
Filter Context Replacement
CALCULATE, this table replaces the existing date filter, causing the measure to evaluate against the shifted period.SAMEPERIODLASTYEAR
DATEADD(<dates>, -1, YEAR). It shifts every date in the current filter exactly one year backward — no parameters needed beyond the date column.DATEADD
CALCULATE as the Orchestrator
CALCULATE, which re-evaluates the base measure under the modified context.CALCULATE is the camera body that accepts whichever lens you mount — SAMEPERIODLASTYEAR gives you a fixed 'one-year-back' lens, while DATEADD lets you dial in any focal shift you need.Visual Explanation — Filter Context Shifting
SUM(Sales[Amount]) — only the filter changes.Notice that both the base measure and the prior-period measure reference the exact same expression — SUM(Sales[Amount]). The only difference is the filter context under which the expression is evaluated. This is the power of DAX's evaluation model: by manipulating the context rather than rewriting the calculation, you achieve clean separation between business logic (what to measure) and temporal logic (when to measure it). This composability is analogous to the decorator pattern in software engineering — you wrap a function with additional behavior without modifying the function itself.
Function Syntax & Evaluation Mechanics
SAMEPERIODLASTYEAR Syntax
DATEADD(<dates>, −1, YEAR).DATEADD Syntax
Measure Pattern: Year-over-Year (YoY) Change
[Sales LY] is CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Dates'[Date])). The DIVIDE function is preferred over the / operator because it handles division by zero gracefully by returning BLANK() or a specified alternate result.DATEADD Intervals — Flexible Time Shifting
While SAMEPERIODLASTYEAR provides a single, hardcoded time shift (−1 year), DATEADD is the generalized form that supports arbitrary shifts across four interval types. Understanding how each interval type maps the current date range to a shifted range is critical for building robust measures. The table below summarizes the most common configurations, and the diagram that follows visualizes how month- and quarter-level shifts behave differently from year-level shifts.
| Use Case | DAX Expression | Shift Description |
|---|---|---|
| Prior month | DATEADD('Dates'[Date], −1, MONTH) | Shifts every date in the filter back by one calendar month. |
| Same quarter last year | DATEADD('Dates'[Date], −4, QUARTER) | Shifts four quarters back, equivalent to one year at the quarter grain. |
| Same month last year | DATEADD('Dates'[Date], −12, MONTH) | Shifts 12 months back — functionally identical to SAMEPERIODLASTYEAR for monthly granularity. |
| Prior 7 days (rolling) | DATEADD('Dates'[Date], −7, DAY) | Shifts each date back by 7 days; useful for week-over-week analysis. |
| Two years ago | DATEADD('Dates'[Date], −2, YEAR) | Shifts two full years back; useful for trend lines spanning multiple years. |
DATEADD shift: −1 MONTH (cyan) yields February 2024 (note the leap-year 29-day month), −1 QUARTER (violet) yields December 2023, and −1 YEAR (pink) yields March 2023 — identical to SAMEPERIODLASTYEAR.Worked Example — Year-over-Year Sales Dashboard
Suppose you have a Power BI model with a Sales fact table containing columns [OrderDate] and [Amount], and a dedicated Dates dimension table with a contiguous date range from 2022-01-01 through 2024-12-31, marked as the date table. A slicer on the report selects June 2024. Your goal is to create three measures: current-period sales, prior-year sales, and year-over-year percentage change.
Total Sales = SUM(Sales[Amount])
With the slicer set to June 2024, this measure scans the Sales table for rows where OrderDate falls in June 2024.CALCULATE with SAMEPERIODLASTYEAR as the filter modifier:
Sales LY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Dates'[Date]))
The engine takes the current filter (June 2024 dates) and shifts each date back one year, producing the set {2023-06-01 … 2023-06-30}. It then evaluates [Total Sales] under this shifted context.DIVIDE function to safely compute the percentage change:
YoY % Change = DIVIDE([Total Sales] - [Sales LY], [Sales LY])
Substituting values: ($142,500 − $128,000) ÷ $128,000 = $14,500 ÷ $128,000 ≈ 0.1133.Sales PQ = CALCULATE([Total Sales], DATEADD('Dates'[Date], −1, QUARTER))
This shifts June 2024 dates back by one quarter, resolving to March 2024 dates. The result reflects prior-quarter performance, useful for seasonality-adjusted analysis.SAMEPERIODLASTYEAR vs. DATEADD — Strengths & Trade-Offs
Both functions accomplish similar goals, but they occupy different points on the simplicity–flexibility spectrum. Choosing between them depends on the analytical requirements and the maintenance cost you are willing to accept. The table below provides a structured comparison across several dimensions.
| Dimension | SAMEPERIODLASTYEAR | DATEADD |
|---|---|---|
| Parameters | One (date column only) | Three (date column, interval count, interval type) |
| Direction | Backward only (always −1 year) | Forward or backward (positive or negative integer) |
| Interval Types | YEAR only | DAY, MONTH, QUARTER, YEAR |
| Readability | Self-documenting; intent is immediately obvious | Requires reading three arguments to understand intent |
| Maintainability | Low maintenance; no parameters to misconfigure | Medium maintenance; incorrect interval or sign introduces subtle bugs |
| Use Case Fit | YoY comparisons exclusively | MoM, QoQ, WoW, multi-year, and custom-period comparisons |
| Performance | Identical — compiled to the same internal plan | Identical when parameters match |
array.isEmpty() and array.length === 0 — same semantics, but the named version communicates intent more efficiently.Connection to Advanced Time Intelligence
The functions introduced in this lesson — SAMEPERIODLASTYEAR and DATEADD — are entry points into a broader family of DAX time-intelligence functions. As your analytical requirements become more sophisticated, you will encounter functions that accumulate values over time, compute running totals, or identify specific date boundaries. The table below previews these advanced counterparts and positions the introductory functions within the larger ecosystem.
| Introductory Function | Advanced Counterpart | What It Adds |
|---|---|---|
SAMEPERIODLASTYEAR | PARALLELPERIOD | Shifts by a full period (e.g., entire year) rather than the same date range — useful for comparing full-year totals regardless of current selection. |
DATEADD | DATESINPERIOD | Returns a contiguous set of dates from a starting point — enables rolling windows (e.g., trailing 30-day average). |
| YoY absolute change | TOTALYTD / DATESYTD | Accumulates values from the start of the year to the current date — year-to-date running totals. |
| Simple % change | PREVIOUSMONTH / PREVIOUSQUARTER | Convenience wrappers for specific period granularities, similar in spirit to SAMEPERIODLASTYEAR but for different intervals. |
As you progress, you will also explore custom date tables with fiscal calendars (e.g., 4-4-5 retail calendars), which require the more flexible DATEADD or even manual filter generation with FILTER and ALL. Mastering the introductory functions in this lesson builds the conceptual foundation — filter context replacement, date table dependency, and the CALCULATE pattern — that underpins every advanced time-intelligence technique.
Practice Problems
SAMEPERIODLASTYEAR require a properly configured, contiguous date table. What would happen if the date table contained gaps (e.g., missing weekends)?Total Revenue = SUM(Orders[Revenue]), write a DAX measure called Revenue LY that returns the same metric for the corresponding period one year ago. Then write a second measure Revenue MoM that returns the metric for the prior month.[Total Profit] that returns $580,000 for Q3 2024 and $510,000 for Q3 2023. Write a single DAX measure Profit YoY % that computes the year-over-year percentage change, and calculate its expected value. Explain how DIVIDE handles the case when Q3 2023 profit is zero.[Sales Amount] and a date table 'Calendar'[Date]. For measure (d), decide whether DATEADD or a different approach is needed, and justify your choice.SAMEPERIODLASTYEAR to compute YoY comparisons. Will the function produce correct fiscal-year-over-fiscal-year results, or will it compare calendar years? Analyze the underlying mechanism and propose a solution if the function falls short.Lesson Summary
This lesson introduced two foundational DAX time-intelligence functions for prior-period analysis. SAMEPERIODLASTYEAR is a single-parameter convenience function that shifts the current date filter exactly one year into the past, while DATEADD generalizes the concept by accepting an interval count and type (DAY, MONTH, QUARTER, YEAR), enabling month-over-month, quarter-over-quarter, and custom-period comparisons. Both functions return a table of shifted dates and must be used as filter arguments inside CALCULATE, which replaces the current filter context with the shifted dates and re-evaluates the target measure.
Key prerequisites include a contiguous date table marked in the model and the use of DIVIDE for safe percentage calculations. The standard YoY pattern — DIVIDE([Measure] − [Measure LY], [Measure LY]) — is a reusable template that extends to any interval. Looking ahead, these introductory functions form the foundation for advanced techniques such as PARALLELPERIOD, DATESINPERIOD, and year-to-date accumulators like TOTALYTD.