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.
I am using a measure found in a Microsoft introduction to Dax to calculate prior YTD Sales-Values:
[Sales].(Dateadd(Datesytd(DimDate(Date),-1,year).
Now if the last working day in a year is the 30.12, and in the year before it is the 31., then this measure will “overlook” the last day because it only shifts the values from the first to the last date value it finds in the fact table?
Mmmh, I created I small fact table in Excel and “linked” it to powerpivot, then loaded my usual dates table and created a relationship via date. Now it apparently does not matter whether I use parallel period or dateadd, the measures always calculate the full year, even if the current year has just 2 rows in the facttable with dates from 2 month…
Yup. Getting the same thing. I have sales table with [REVENUE] and [INVOICE DATE]. Invoice Date is linked to DimDate[DimDate]. If I use ParallelPeriod with DimDate, I get 2010 Sales, AND if I use DateAdd with DimDate, I get …. 2010 Sales. If I try to use DateAdd with Sales(Invoice Date), I get an error because DateAdd can only work with contiguous date selections. I know I don’t have 365 consecutive dates worth of sales.
So, with my Sales[Invoice Date] linked to DimDate[Dim Date], exactly why are we getting the same results between ParellelPeriod and DateAdd?
Hello,
The problem occurs if you would do a
=CALCULATE([Sum of SalesAmount],DATEADD(DimDate[FullDateAlternateKey],-1,YEAR))
for 2010 where 2010 does not have data for month 11 and 12. The values it will calculate for 2009 are from jan to oktober. Parallelperiod would take Jan to December. So you will see a difference between Parallelperiod and DATEADD -1 year for 2010.
Ofcourse you will only see the difference if you have data in nov and december for 2009.
Hope this helps,
Kasper
Is there a way to use PARALLELPERIOD and/or DATEADD to get a rolling 3-month total for non-consecutive months? For example, let’s say customer XYZ has has for Oct., Nov., and December. If I wanted to get a standard 3 month rolling, I could click on December and assuming I wrote my DAX function using a couple of PARALLELPERIODs …+(…-1, month)+(…-2,month), get the 3 month rolling. However, let’s say that company ABC had data for August, November, and December. Is there way way I can write a formula to pull data from the last 3 months that have it? So if I click on December (and there’s only data for December/November/August), it will return the 3 month aggregation for those 3 months b/c they’re the previous 3 months with data. If I click on December and there’s no data (but there is for say only November, October, and August), it’ll pull for Nov./Oct./August. Any ideas?
@Joshua
Hi Joshua, same problem. Have you figured it out?
Thanks
Kasper, good day!
In your blog about “dateadd” and “parallelperiod” you do write that there will be difference between two functions if current year fact table has date from jan to jul
In your book on page 59 you write … “dateadd” will use the entire year
Please, help me
in my file with fact table till jul 2014 there is no difference between these two functions
thank you for your work
Hi, thanks for reading the book. In this case the devil is in the details.
PARALLELPERIOD does exactly what you tell it to do where DATEADD is more depending on the data.
Hope this text in MSDN explains it best:
The PARALLELPERIOD function is similar to the DATEADD function except that PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. For example, if you have a selection of dates that starts at June 10 and finishes at June 21 of the same year, and you want to shift that selection forward by one month then the PARALLELPERIOD function will return all dates from the next month (July 1 to July 31); however, if DATEADD is used instead, then the result will include only dates from July 10 to July 21.
Hi Kasper,
I’m trying to create PMDate (Previous Month Date) calculated column for each day in my Date dimension. For example, it will give me previous month’s first week’s Wednesday’s date for the current month’s first week’s Wednesday.
ı have attached a screenshot from my model.
Could you please help me ?