Dax Function: SAMEPERIODLASTYEAR
Category: Time Intelligence Functions
The SAMEPERIODLASTYEAR function in Power BI is a DAX time intelligence function that returns a table of dates from the same period in the previous year. It is particularly useful for performing year-over-year (YoY) comparisons for a given time frame, such as a month, quarter, or year.
Purpose of the Function
The purpose of SAMEPERIODLASTYEAR is to simplify YoY analysis by providing a direct way to retrieve data for the same period in the preceding year. It is commonly used in:
- Performance comparisons across years for the same time period.
- Evaluating trends in business metrics like sales, revenue, or profits.
- Generating reports and dashboards that include YoY growth rates.
Type of Calculations
- Comparison: Calculate metrics for the same period in two different years.
- Trend Analysis: Identify patterns or fluctuations between the current and previous year.
- Performance Metrics: Compute YoY changes in KPIs like revenue or customer count.
Practical Use Cases
- Revenue Tracking: Compare revenue for March 2025 against March 2024.
- Seasonal Analysis: Assess patterns during high-demand periods, like holidays, across years.
- Operational Metrics: Monitor year-over-year changes in production or service delivery.
SAMEPERIODLASTYEAR(<dates>)
| Parameter | Type | Description |
|---|---|---|
dates | Column | A column containing date values, typically from a date table in your data model. |
How Does SAMEPERIODLASTYEAR Dax Function Works
- Current Context: The function identifies the date range in the current filter context.
- Shift to Previous Year: It computes the equivalent date range for the preceding year.
- Return Date Table: The function outputs a table of dates corresponding to the calculated range.
For instance, if the current filter context is April 1–April 30, 2025, SAMEPERIODLASTYEAR will return April 1–April 30, 2024.
What Does It Return?
The SAMEPERIODLASTYEAR function returns a table of dates representing the same period in the previous year. The exact range depends on the current filter context, such as a specific month, quarter, or year.
When Should We Use It?
- Comparing Performance: Ideal for creating YoY comparison reports.
- Time-Frame Specific Analysis: Useful when focusing on the same months, quarters, or weeks across different years.
- KPI Monitoring: Helps in tracking progress or decline in key business metrics.
Examples
Basic Usage
Sales Same Period Last Year = CALCULATE(
SUM(Sales[Amount]),
SAMEPERIODLASTYEAR(Dates[Date])
)
This measure calculates total sales for the same period in the previous year.
Column Usage:
Customer Retention YoY = CALCULATE(
COUNT(Customer[CustomerID]),
SAMEPERIODLASTYEAR(Dates[Date])
)
Counts the number of customers retained during the same period last year.
Advanced Usage
YoY Revenue Growth = DIVIDE(
SUM(Sales[Amount]) - CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Dates[Date])),
CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Dates[Date]))
)
Calculates the percentage growth in revenue compared to the same period last year.
Tips and Tricks
- Use a Complete Date Table: Ensure the date table is properly configured with no missing dates to avoid errors.
- Combine with Filters: Use alongside
FILTERorCALCULATEfor refined comparisons. - Be Context-Sensitive: This function relies heavily on the current filter context. Ensure your filters are set appropriately.
- Time Period Matching: To ensure accurate comparisons, the date table should be granular enough to match the desired time frame (e.g., days, months, quarters).
Potential Pitfalls:
- The function strictly adheres to the Gregorian calendar. For fiscal year-based analysis, consider using custom logic.
- Misconfigured or incomplete date tables may produce incorrect or blank results.
Performance Impact of SAMEPERIODLASTYEAR DAX Function:
- Optimized for Queries: The function performs efficiently with indexed and well-structured date tables.
- Avoid Complex Nesting: Combining it with multiple filters or calculations may impact performance on large datasets.
Related Functions You Might Need
PREVIOUSYEAR: Returns all dates from the entire previous year.PARALLELPERIOD: Shifts a date range by a specified number of intervals (e.g., months, quarters, years).PREVIOUSMONTH: Retrieves dates for the previous month.DATEADD: Allows shifting the date range by custom intervals.TOTALYTD: Calculates year-to-date totals, often paired withSAMEPERIODLASTYEARfor comparisons.
Want to Learn More?
For more information, check out the official Microsoft documentation for SAMEPERIODLASTYEAR. You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
It returns a table of dates corresponding to the same period in the previous year, useful for YoY analysis.
No, it uses the Gregorian calendar. For fiscal calendars, you must customize your date table.
SAMEPERIODLASTYEAR matches the current period (e.g., month, quarter) in the prior year, while PREVIOUSYEAR retrieves all dates from the previous year.
Misconfigured date tables or gaps in date continuity can result in incorrect or blank results.
No, the function is designed specifically for date fields and requires a valid date column.
Established in 2020, Lets Viz Technologies provides a full range of high-quality data analysis and data visualization services. We are also an authorized Zoho Partner.
Sitelinks
