DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

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:

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.

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:

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 can be used to fetch the Sales of last month like this:

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 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.
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

2 Comments

  • 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.

Leave a Reply

Your email address will not be published. Required fields are marked *