Have You Ever Seen This Cryptic Error? Here’s a Quick Explanation and Fix 
So you’ve got a DATEADD measure…
Let’s say you’ve written a nifty measure called [Last Year Sales] that returns the sales value you had one year ago at the same time:
[Last Year Sales] =
CALCULATE([Total Sales, DATEADD(Calendar[Date], –1, Year))
And hey, that works great:
So far so good: [Last Year Sales] Measure is Working Great!
Aside: the importance of a Calendar/Date table
Of course, even to get this far, you must have a separate Calendar/Date table. See this post and this post for more info on that.
OK, now back to the topic at hand.
Rearrange pivot. All hell breaks loose.
Now you rearrange that perfectly working pivot by dragging Month to Columns…
All I Did Was Drag Month to Columns!
And boom! The whole pivot blows up. I get the contiguous date ranges error.
Whaddya mean? My dates ARE contiguous!
If I take the DATEADD measure off of the pivot, and just look at Total Sales, it is very hard to understand where things are “dis-contiguous.”
Did I Skip a Year? Did I Skip a Month? I Don’t THINK So. So Where’s the Problem?
I think the confusion starts here: where is the discontiguous date range you speak of, oh Power Pivot formula engine?
The Dis-Continuity Occurs in the Grand Totals!
Take a look at the highlighted cell below and tell me what date ranges are reflected there:
January 2001, January 2002, etc. Aha! Dis-Contiguous!
That’s just dirty, isn’t it. The grand total cells sneak gaps into our date ranges when we arrange the pivot certain ways.
Now, personally, I believe DATEADD should be more robust, and simply return BLANK() in those cases rather than an error. But that’s a fight for another day isn’t it?
So what do we do?
Turning Off Grand Totals Does NOT Fix It
Nice Try. But This Won’t Save You.
I tried this myself years ago. It doesn’t help.
Turning off the display of grand totals only tells Excel not to display them. Power Pivot still calculates them, and hits the error. Bah humbug.
IFERROR to the rescue? Nope.
Now this one is really silly IMO. If I wrap an IFERROR() around the CALCULATE, I still get the error!
WTF??? (What the Formula???) I dunno. I’m moving on.
Here is A Fix That Works
I’m not sure this is the best fix, and I am positive it is not the only fix. If you’ve got a better one, please suggest.
Basically, it’s the old “suppress grand total evaluation” trick.
[Last Year Sales] =
IF(HASONEVALUE(Calendar[CalendarYear]),
CALCULATE([Total Sales],
DATEADD(Calendar[Date], -1, Year)
),
BLANK()
)
So… if we’re dealing with a pivot cell that “spans” more than one year, we return BLANK().
Which, really, we probably should anyway. I mean, if we’re looking at 2001 and 2002 combined, what does “last year” mean? 2000? 2001? Some hybrid of the two? It’s not semantically sensible in most cases.
Anyway, that fix works:
Ah, no more errors. And the grand totals are suppressed by the formula.
Anyway, I hope this helps those of you who stumble across this intimidating error.
