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] =

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],
),
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.

#### Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

## This Post Has 6 Comments

Couldn’t you do a SUMX over the datetable? That way you are ensuring you are never in a discontiguous date range?

Actually I agree with your statement that “Last Year” doesn’t make sense in this context. 🙂

3. Stijn says:

The dateadd function is one I often use in P&L reporting and of course, the error also occurs in my pivots. My trick is to first add a slicer (Year/month) and select a period for which a result is available in an earlier period. If I then add the new measure with a dateadd function, Power Pivot doens’t complain.

4. Krasni says:

Thanky you very much, you solved my problem!

5. WIlliam says:

This is excellent!

Thank you!