The ‘Function XXXX only works with contiguous date selections’ Error

In my last post on DAX, I mentioned I’d come across the following error when running a query in PowerPivot:

ERROR – CALCULATION ABORTED: MdxScript(Sandbox) (22, 4) Function ‘DATEADD’ only works with contiguous date selections.

The DAX expression I was using was as follows:

    , DATEADD(DimDate[FullDateAlternateKey], -1, YEAR)
    , ALL(DimDate))

And the error was happening when I ran queries with Dates on either rows or columns and was using a slicer that had multiple years selected but only a few months, for example like this:


Clearly I had a non-contiguous date selection: I was expecting to see all the dates in January and February 2003 on rows, followed by all the dates in January and February 2004. But I couldn’t understand why I was getting an error on what, to me, seemed like a perfectly reasonable query!

So I asked my friends at Microsoft and Marius Dumitru explained that this wasn’t really an error, it was actually a deliberate feature designed to stop users running queries where calculations would return misleading results, or where the semantics of what was actually happening would be hard to understand. I can understand the motivation for doing this – for example, consider what would happen in the scenario above if there was no error, and I didn’t have dates on rows or columns – no time intelligence calculation would return any meaningful values here. But I still maintain that my scenario above should return values, because in this case the slicers are only serving to filter which dates are being displayed on a visible axis, and when the dates themselves are visible on an axis then it makes sense to see the result of the calculation.

Time to open a Connect, then, in the hope that this scenario will be allowed in a future version. Please vote here:

5 thoughts on “The ‘Function XXXX only works with contiguous date selections’ Error

  1. Rob Collie and I are working on this problem as well, we agree that this should return a value. A workaround is to check if one year is selected and show values only then, not what you want but it makes it workable.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.