Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

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

with 2 comments

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:

=CALCULATE(SUM(FactInternetSales[SalesAmount])
    , 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:

image

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:
https://connect.microsoft.com/SQLServer/feedback/details/565032/loosen-restrictions-on-contiguous-date-selections-error

Written by Chris Webb

June 3, 2010 at 10:06 pm

Posted in DAX

2 Responses

Subscribe to comments with RSS.

  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.

    Kasper

    June 4, 2010 at 12:15 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,301 other followers

%d bloggers like this: