Something that might be interesting to know is that DATEADD and PARALLELPERIOD look very similar but do behave differently. This blog posts looks at the difference between the two. Lets look at these two formula’s:
=CALCULATE([Sum of SalesAmount], DATEADD(DateTable[FullDateAlternateKey],-1,YEAR)) =CALCULATE([Sum of SalesAmount], PARALLELPERIOD(DateTable[FullDateAlternateKey],-1,YEAR))
They both look very similar right ? But what is the difference ? Lets look at the results:
The first and only thing you see here is the difference of 2004. Why ? To determine previous year DATEADD uses a range between the first date where you have values in the facttable to the last date where you have values in your facttable for the current year on rows, it will this range to go back one year. Whereas PARALLELPERIOD uses the first and last date in your datetable for the current year to go back one year.
What ? Ok let’s look at it in a table for DATEADD :
I added two measures that will show you what the first and last date are that being used to go back one year. For 2004 you can see that it uses the startdate 1/1/2004 as this was the first date a sale was made in that year. But as last year we see 8/31/2004 because this was the last date a sale was done in that year (the year is not finished yet). If you now go back one year it will calculate the sum of SalesAmount for 1/1/2003 to 8/31/2003.
Now look at the first and lastdates PARALLELPERIOD uses. As you can see the it determines the current year, looks for the first and last day in the year using the date table and use that to move back one year. Going back one year it will calculate the sum of SalesAmount for 1/1/2003 to 12/31/2003. Giving a larger result.
Ok looks like PARALLELPERIOD is the way to go right ? Well it depends on what you want to do. PARALLELPERIOD really goes back one entire year (based on what you select in the parameter) using the first and last date. DateAdd uses the first and last date in the current selection. What does this mean ? We can best illustrate this by adding months into the mix:
What makes these two measures return different results here is that both use a different mechanism to determine the current period. Where PARALLELPERIOD always goes back one entire year (or whatever parameter you select in the measure), DATEADD will look at the current row selection and go back one year for that selection. In this final image for the row with month 04/2004 DATEADD will show you the sum of SalesAmount for 04/2003 where PARALLELPERIOD will go back one entire year.