Defanging the “contiguous date selections” error

Calculation Aborted.  Function DATEAD only works with contiguous date selections.  Power Pivot, why you gotta be so cryptic?

Have You Ever Seen This Cryptic Error?  Here’s a Quick Explanation and Fix Smile

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:

image

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…

image

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.”

image

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:

image

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

image

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:

image

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.

4 Responses to Defanging the “contiguous date selections” error

  1. Konrad says:

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

  2. Konrad says:

    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!

Leave a Comment or Question