DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question

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 on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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.

20 thoughts on “DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question

  • useful article. but i need to do calculations like
    e.g. date:11/29/2018
    total sales 11/29/2018 vs total sales 11/29/2017
    so for a specific date..
    It will always be today()-1

    Please let me know

    • for that you can use the SAMEPERIODLASTYEAR function
      You would need a table that shows dates, and then a measure with the SamePeriodLastYear function as mentioned in this post.

  • Hello Reza,
    I have table with Complaint Forward date and i want to calculate due date and i tried Dateadd but i am unable to find the Working days. ( I want the due date with 10 working days) Could you please help.

    Thanks

  • Hi,

    Great post as always!

    Maybe you could add/explain why in a leap year (eks 2020) use “SAMEPERIODLASTYEAR’ will get a duplicate date at 2/29 and hereby also duplicate values on all date level (year, month, day).

    As tested, one should use Dateadd -366, day.

    • Hi Cody
      Same Period Last year is kind of similar to DateAdd -365. It is not exactly correct with leap years. an alternative can be using DateAdd at Day level combined with IF to check is it includes a leap year or not.
      Cheers
      Reza

  • Thanks for sharing. I am just wondering why we need to add .[Date] on the measures. Is it always compulsory to have .[Date] for SamePeriodLastYear and DateAdd functions. Sometimes I don’t see ppl adding .[Date] and they still work. Ady advice?

    Thanks!

  • do either of these functions compare a specific year ( eg 2019) against all the next years? eg 2020 to 2019, 2021 to 2019, 2022 to 2019?

  • if I’m trying to compare daily sales over the last 90 days, and compare them to the same period in a specific year (2019 in this case) how would I combine these to do that?

    • Hi Dan
      Please take a look at the previous dynamic period calculation I explained here.
      For you, instead of last year, it may need to be more dynamic and use the year from the slicer. let m know if you need any help.
      Cheers
      Reza

Leave a Reply