Calculating Prior Year with SAMEPERIODLASTYEAR

1. The Theory: What Does SAMEPERIODLASTYEAR Do?

As its name suggests, SAMEPERIODLASTYEAR is a time intelligence function designed to shift your analysis back by exactly one year.

Crucially, just like the DATESYTD function we discussed previously, SAMEPERIODLASTYEAR does not calculate a value on its own. Instead, it returns a table containing a single column of dates. This table of dates represents the equivalent period from the previous year, based on the current filter context (the dates selected in your visual or slicer).

Because it returns a table, you must use it as a filter argument inside the CALCULATE function. The pattern is always:
CALCULATE( <Your Measure>, SAMEPERIODLASTYEAR(<Your Date Column>) )

Let's watch a short video from the experts at SQLBI that explains this core concept.

This is a critical point that aligns with our theory-first approach: SAMEPERIODLASTYEAR prepares the time-shift; CALCULATE executes the calculation for that new time period.

2. Practical Application: Creating Your 'Prior Year Sales' Measure

Now, let's put this theory into practice in your Power BI file. We will create a measure to calculate the total revenue for the same period last year.

Follow these steps:

1. In Power BI, create a New Measure.

2. Name it Sales PY (for Prior Year).

3. Enter the following DAX formula:

   Sales PY = 
   CALCULATE(
       [Total Revenue],
       SAMEPERIODLASTYEAR('Date'[Date])
   )
   

How to Verify Your Measure:

To see if it's working correctly, create a Matrix visual with the following fields:

• Rows: 'Date'[Year] and 'Date'[Month Name]
• Values: [Total Revenue] and [Sales PY]

Expand the years in your matrix. You should see that the Sales PY value for any given month is the same as the Total Revenue value for that same month in the previous year. For example, the Sales PY for July 2008 should match the Total Revenue for July 2007.

The image below illustrates this perfectly.

3. Understanding the "Same Period" Logic

The most powerful feature of this function is the "Same Period" part. It dynamically adapts to the level of detail in your visual.

• If your table is summarized by month, it fetches the sales for the same month last year.
• If you change your table to be summarized by quarter, the same measure will automatically fetch the sales for the same quarter last year. You don't need to write a new formula.

This dynamic behavior makes your measures incredibly flexible and reusable. The following video segment demonstrates this concept beautifully.

4. A Deeper Look: The "Syntactic Sugar" Connection

In our last lesson, we learned that TOTALYTD is "syntactic sugar" for a more fundamental CALCULATE expression. The same is true here! SAMEPERIODLASTYEAR is also a convenient shortcut for another, more versatile function: DATEADD.

The expression:
SAMEPERIODLASTYEAR('Date'[Date])

is exactly equivalent to writing:
DATEADD('Date'[Date], -1, YEAR)

This is a fantastic insight from the "why" perspective you appreciate. It reveals that SAMEPERIODLASTYEAR is simply a specific instruction to the more general DATEADD function, telling it to "go back 1 year". This answers the question, "What if I want to go back two years, or just one month?" The answer lies in using DATEADD directly, which we will explore in our very next lesson.

This relationship is confirmed by DAX experts. For instance, an article on SQLBI titled "Differences between DATEADD and PARALLELPERIOD in DAX" explicitly states this equivalence. We will not read the full article now, as it covers more advanced topics, but knowing this connection deepens your understanding of how DAX functions are related.

Conclusion

You have now successfully created one of the most common and powerful comparison measures in data analytics. You can now look at any period and instantly see how it performed against the same period in the prior year.

Key Takeaways:

• SAMEPERIODLASTYEAR is a table function that returns the equivalent period from one year ago.
• It must be used inside CALCULATE to modify the filter context and compute a measure for that past period.
• The function is dynamic and automatically adapts to the time granularity (day, month, quarter) of your visual.
• It is "syntactic sugar" for the more general DATEADD('Date'[Date], -1, YEAR) function.

Preview of the Next Lesson:
We've calculated Total Revenue and Sales PY. In the next lesson, we will combine these two measures to calculate Year-over-Year (YoY) Growth, both as an absolute value and as a percentage. We will also dive into the DATEADD function to create a Prior Month measure, which will allow us to calculate Month-over-Month (MoM) Growth. You are now moving firmly into advanced business calculations