Power Bi offers a range to time intelligence functions to manipulate and analyze date and time data effectively. Among these functions, SAMEPERIODLASTYEAR(), PARALLELPERIOD(), and DATEADD() are commonly used to compare data over different time periods. Here’s a closer look at the differences and applications of each:
PARALLELPERIOD()
PARALLELPERIOD() is a flexible function that allows you to shift a time period forward or backward by a specified number of intervals. It supports various intervals like months, quarters, or years, making it suitable for broader time comparisons.
Syntax:
PARALLELPERIOD(<dates>, <number_of_intervals>, <interval>)
Use Case: Previous Year Sales
Previous Year Sales= CALCULATE( [TOTAL SALES], PARALLELPERIOD(_date[Date], -1, YEAR))
Notice unlike SAMEPERIODLASTYEAR(), PARALLELPERIOD() shifts the whole period. Even though the max date in the dataset is 31/05/2005, the above calculation looks at the whole period.
Other use cases of PARALLELPERIOD() are Previous Month, Previous Quarter, Same Month Last Year.
Use Case: Same Month Last Year Sales
Sales Same Month Last Year = CALCULATE( [Total Sales] , PARALLELPERIOD(_date[Date], -12, MONTH))SAMEPERIODLASTYEAR()
SAMEPERIODLASTYEAR() is a straightforward function designed to retrieve the same period in the previous year.
It is used for year-over-year comparisons, such as comparing sales data for the same month or quarter across different years.
Syntax:
SAMEPERIODLASTYEAR(<dates>)
Use Case: Previous Year to Date Sales
Sales Last Year = CALCULATE(SUM(Sales[Sales Amount]), SAMEPERIODLASTYEAR(Sales[Date]))
Can see that for 2005 the PYTD sales are smaller than the Total Sales for 2004. This is because the data for 2005 only goes until May, so the SPLY calculation is only looking at Jan - May 2004.
The PYTD sales value for 2004 is the same as Total Sales for 2003 is because in our date table, everyday of 2004 is populated, so it looks at the whole of 2003 for the PYTD value.
Use Case: Same Month Last Year Sales
Sales Last Year = CALCULATE(SUM(Sales[Sales Amount]), SAMEPERIODLASTYEAR(Sales[Date]))
You can use the same calculation for PYTD to look back on Same Month Last Year as well. If we take the above table and swap Year for Month-Year.
Without changing the calculation, it has dynamically changed to a monthly level. The value for 2004-Nov is now the Sales amount for 2003-Nov.
DATEADD()
DATEADD() is another versatile function that shifts a date by a specified number and type of interval.
It is similar to PARALLELPERIOD(), but with a slightly different approach. It’s particularly useful for creating measures that require date shifts in more granular increments (e.g. days).
Syntax:
DATEADD(<dates>, <number_of_intervals>, <interval>)
Use Case: Previous Year Sales
Previous Year Sales = CALCULATE( [Total Sales] , DATEADD(_date[Date], -1, YEAR))
Similarly to SAMEPERIODLASTYEAR(), it just looks at the relevant period which is why we see £1.3million for 2005.
Use Case: Same Month Last Year Sales
Sales Same Month Last Year = CALCULATE( [Total Sales] , DATEADD(_date[Date], -1, YEAR))
NOTE that the calculations above are the same. This is because DATEADD() will dynamically adapt to what's in the view, again similarly to SAMEPERIODLASTYEAR().
Summary
- SAMEPERIODLASTYEAR(): Simplest function for year-over-year comparisons, works only with exact annual periods.
- PARALLELPERIOD(): Flexible, allows shifting by various intervals (months, quarters, years), suitable for more customized period comparisons.
- DATEADD(): Most versatile, allows shifts by any interval (days, weeks, months, quarters, years), providing granular control over time-based data manipulation.
