Forum Discussion
Function 'DATEADD' only works with contiguous date selections.
CraigBlackmanYou can try P/Y Sales = CALCULATE ( [Net Sales]; SAMEPERIODLASTYEAR( Dates[Date]))
or wrap your P/Y Sales with SUMX
P/Y Sales = SUMX ( salestable; calculate( [Net Sales]; DATEADD( Dates[Date]; -1 ; Year ))
CraigBlackmanYou can try P/Y Sales = CALCULATE ( [Net Sales]; SAMEPERIODLASTYEAR( Dates[Date]))
or wrap your P/Y Sales with SUMX
P/Y Sales = SUMX ( salestable; calculate( [Net Sales]; DATEADD( Dates[Date]; -1 ; Year ))
After a bit of trial and error, the SUMX wrap has done the job, so many thanks for your help. Greatly appreciated.
Craig Blackman
Hi all, new to the forum and new to DAX as well. It seems like many people have this same question, yet I haven't yet been able to find an answer that works in my situation.
What I want to do. Compare sales year to date for current year (for example 1/1/2015 to 11/2/2015) to sales year to date previous year (for example 1/1/2014 to 11/2/2014)
Here's my two formulas so far
Sales YTD:=TOTALYTD(sum(Sold[ExtPrice]),Sold[OrderDate])
Sales Prev YTD:=CALCULATE(SUM(Sold[ExtPrice]),DATESBETWEEN(Calender[DateKey],date(2014,1,1),LASTDATE(DATEADD(Sold[OrderDate],-365,DAY))))
My formula works fine in PowerPivot View, but I can't get around the dreaded contiguous date error whenever I create a pivot table. I've tried messing with various IF(HASONEVALUE.... and SUMX... but haven't gotten a solution. Again, I'm pretty new to all this so my understanding of some of these functions is pretty limited. Any help would be greatly appreciated.
sksmith It seems that you are pulling a date for your Sales YTD from the fact table and not from your calendar table. Try:
Sales YTD: = TOTALYTD(SUM(Sold[ExtPrice]), Calender[DateKey]))
Do the same for your other calculations. Pull the date from the date table as time intelligence needs the date table to function correctly.
