As a beginner in Power BI and DAX, it’s easy to get overwhelmed by the many functions available in DAX. For example, when studying time intelligence functions, I encountered challenges with three specific functions: DATEADD, SAMEPERIODLASTYEAR, and PARALLELPERIOD. These functions may sound similar, but they actually have distinct differences. This blog will delve into this topic. First, let’s start by examining the definition of each function.

DATEADD:

Similar to Tableau’s Dateadd function, DATEADD enables the addition or subtraction of a specified number of days, months, quarters, or years from a given date field. Its syntax is as follows:

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

SAMEPERIODLASTYEAR:

SAMEPERIODLASTYEAR does exactly what it says—it helps you compare the same period from the previous year. If you’re looking at data for a particular day, it gives you the same day from the previous year. If you’re analyzing data at the month or quarter level, it gives you the corresponding month or quarter from the previous year. It’s useful for year-over-year comparisons.

SamePeriodLastYear(<date field>)

PARALLELPERIOD:

PARALLELPERIOD allows you to get the parallel period relative to the current one. You can specify the interval as Month, Quarter, or Year. This function requires three parameters:

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)

It’s worth noting that PARALLELPERIOD operates only on intervals of Month, Quarter, and Year, while DATEADD work on DAY, Month, Quarter, and Year, which is the first distinction between DATEADD AND PARALLELPERIOD. Another important distinction is that PARALLELPERIOD always returns complete periods at the specified granularity, whereas DATEADD may include partial periods. For example, if you have a selection of dates spanning from June 10 to June 21 within the same year and want to shift it forward by one month, PARALLELPERIOD will return all dates from the following month (July 1 to July 31). However, if DATEADD is used instead, the result will comprise only the dates from July 10 to July 21.

Conclusion

DateAdd and SamePeriodLastYear can work dynamically based on the filter context. ParallelPeriod is working STATICALLY based on the interval selected in the parameter.

From the screenshot, you can see Parallelperiod returns the profit for the entire last year 1997, SAMEPERIODLASTYEAR returns the profit for the same period last year based on the filter context which is month here.

 

 

 

The Data School
Author: The Data School