Using DAX time intelligence functions for a while; you may ask this question from yourself that what is the difference between functions below;

• SamePeriodLastYear function vs using ParallelPeriod with Year parameter
• ParallelPeriod for a month vs DateAdd for a month ago
• and many other questions that lead to this final question: Which function should be used in which situation?

Let’s take a look at these questions and their responses in more details through this post. If you want to learn more about Power BI: read Power BI book from Rookie to Rock Star.

SamePeriodLastYear

Let’s start with the SamePeriodLastYear function; this function will give you exactly what it explains; same PERIOD but last year! same period; means if you are looking at data on the day level, it would be same day last year. If you are slicing and dicing in a month or quarter level; this would give you the same month or quarter last year. You can use the function simply just by providing a date field:

SamePeriodLastYear(<date field>)

the image below shows how the SamePeriodLastYear works for Date

The SamePeriodLastYear function like many other time intelligence functions needs a date field to work. this is how you can get this function working:

SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey].[Date])

The code above returns a table with one single column: date. This is not returning one single value. Means you cannot use it directly in a measure. You have to use this function as a filter function. In the screenshot above; I have used the SamePeriodLastYear inside a LastDate, and also a FirstDate to get the range of dates for each filter context selection.

SamePeriodLastYear D To = 
LASTDATE(
SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey].[Date])
)

As you can see in the screenshot above; it shows that the SamePeriodLastYear returns the same date last year when your filter context is in day level. If you filter context is at month level; then you get the same month last year. the screenshot below shows it;

For example; for September 2006, SamePeriodLastYear returns September 2005.

SamePeriodLastYear returns the equivalent period to the filter context from last year. for 1st of Sep 2006, it will return date period of 1st of Sep 2005. For Q4 of 2006 it will return Q4 of 2005. If filter context is in DAY level; it will return the same DAY last year, if the filter context is in Month level, it will return same Month last year.

SamePeriodLastYear function when used in a real-world scenario it will act as a filter, and you can get the Sales of the same period last year with that using an expression like this:

Same Period Last Year = CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    SAMEPERIODLASTYEAR(
        DimDate[FullDateAlternateKey].[Date])
)

ParallelPeriod

ParallelPeriod is another function that gives you the ability to get the parallel period to the current period. You can navigate to periods in the past or future. you need three parameters for this function:

ParllelPeriod(<date field>, <number of intervals>, <interval>)

You can choose the interval to be Month, Quarter, or Year. and the number of intervals can be negative (to go to past), or positive (to go to the future). This is an example of using ParallelPeriod:

For every month, the ParallelPeriod expression will return a month before that, because in the parameters, we mentioned the month before:

PARALLELPERIOD(DimDate[FullDateAlternateKey].[Date], -1, MONTH )

ParallelPeriod can be used to fetch the Sales of last month like this:

Parallel Period -1 Month = CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    PARALLELPERIOD(
        DimDate[FullDateAlternateKey].[Date],
        -1,
        MONTH)
)

As you can see in the above screenshot; ParallelPeriod will return sales of the entire last month, even if you are looking at the day level. This brings us to an important conclusion:

ParallelPeriod gives the result of a period parallel to this period (in the past or future), which is statically determined in the Interval parameter; Can be Month, Quarter, or Year.

Understanding this fact; now we can answer this question:

What is the difference between SamePeriodLastYear and ParallelPeriod?

The first difference is that ParallelPeriod gives you the option to go as many as intervals you want back or forward. If you want to get the sales for last months; then ParallelPeriod is your friend. for calculating the sales of 2 years ago, then ParallelPeriod is your friend.

Dynamic Period is another difference between these two functions; If you think that the result of SamePeriodLastYear and the ParallelPeriod (when it is used with Year interval) are the same, continue reading. below is an example of these two measures:

For August 2006 for example; the SamePeriodLastYear gives us the sales of August 2005. However, the ParallelPeriod with year interval returns the sales for the entire year 2005.

DateAdd

DateAdd is a function that adds or subtracts a number of days/months/quarters/years from or to a date field. DateAdd can be used like this:

DateAdd(<date field>, <number of intervals>, <interval>)

DateAdd used in a example below to return the period for a month ago.

DateAdd can be used in a Day level too. This brings us to the first difference of ParallelPeriod and DateAdd;

DateAdd can work on an interval of DAY, Month, Quarter, or Year, but ParallelPeriod only works on intervales of Month, Quarter, and Year.

This is the example expression to calculate the sales for yesterday:

DateAdd -1 Day = CALCULATE(
    SUM(FactInternetSales[SalesAmount]),
    DATEADD(
        DimDate[FullDateAlternateKey].[Date],
        -1,
        DAY))

DateAdd vs ParallelPeriod

Comparing these two functions with each other; you can see that DateAdd works on the period dynamically (like SamePeriodLastYear), but the ParallelPeriod works statically on the interval mentioned as the parameter.

That leads us to the conclusion that DateAdd(<date field>,-1, Year) is similar to SamePeriodLastYear, however, one difference is still there:

DateAdd vs SamePeriodLastYear

SamePeriodLastYear only goes one year back, DateAdd can go two years back or even more. DateAdd is a customized version of SamePeriodLastYear.

Conclusion

In summary, there are differences between these three functions:

• DateAdd and SamePeriodLastYear both work based on the DYNAMIC period in the filter context
• ParallelPeriod is working STATICALLY based on the interval selected in the parameter
• ParallelPeriod and DateAdd can go more than one interval back and forward, while SamePeriodLastYear only goes one year back.
• DateAdd works on the interval of DAY, as well as month, quarter and year, but ParallelPeriod only works on month, quarter, and year.
• Depends on the filter context you may get a different result from these functions. If you get the same result in a year level context, it doesn’t mean that all these functions are the same! Look more into the detailed context.

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.