By Dany Hoter

(Download file here to follow along).

Time intelligence functions are some of the most important functions in DAX.

Being able to compare values between current period and the same period last year is a very common request and one that is a real challenge using native Excel and can easily achieved with DAX.

A typical calculated field might look like:

Sales Last Year:=CALCULATE(
  [Sales]  ,SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])
)

In quite a few cases once you start using such expressions you will see this annoying error message:

image

The reason is that the time intelligence functions like the one used here SAMEPERIODLASTYEAR require 100% completely consecutive dates in the filter context, with no holes.

The problem is that it is not always clear where is the non-consecutive range of days.

In the attached example, you open the Workbook and everything seems to work fine.

If you try to drag the calculated field [UnprotectedLAstSales] to the pivot you’ll get the error.

There are no active slicers at this point so it is not clear why the error situation is triggered.

The reason is the cross-filtering effect of slicers. Excel is checking by default on each slicer which items are empty based on the pivot(s) content , all the measures used in all pivots and what is filtered in all other slicers.

image
Not just the Pivot, the cross-filtering of slicers may yield
Non-Consecutive dates and hence this error

Cure

If you go to slicer settings in the two slicers and uncheck the setting: “Visually indicate items with no data”, you will be able to drag the calculated field from the last step and will not see the error.

clip_image003

If you try and filter a single value in either one of the slicers the error message will reappear.

This time it is rather easy to explain why.

When you select one day of the month let’s say the first of the month, the actual set of days will include the first of each month across a few years span.

This is obviously a non-consecutive range and that’s why the error appears.

In the same way when Excel checks each member in the slicer to see if it is empty it actually uses a non-consecutive range of days for each day of the month.

Total Cure for the Problem

In order to make sure that this error does not appear ever we can change the calculation in this way:

We’ll add a new calculated field:

Holes:=value(lastdate(DimDate[FullDateAlternateKey]))-value(firstdate(DimDate[FullDateAlternateKey]))+1-countrows(dimdate)

This field will return 0 if the list of days is consecutive.

Using it I protect the original field and return blank if the range is not consecutive:

Last Year Sales:=if([Holes]<>0,BLANK(),CALCULATE([Sum of Sales],SAMEPERIODLASTYEAR(DimDate[FullDateAlternateKey])))

Now the worst that can happen is that a cell in the pivot will be empty when it doesn’t make sense anyhow.

Strange Phenomena

clip_image005

When I removed the current sales and use only last Year Sales (I agree that it doesn’t make a lot of sense) all items in the two slicers show as empty.

The reason is that the protected calculation returned blank for all the items and as it was the only measure used in the pivot the verdict for each items is that it is indeed empty.

The year 2006 also disappeared from the pivot because it doesn’t have sale a year before and it is a normal behavior for the pivot to remove empty rows.

Download file here.

  Subscribe to PowerPivotPro!
X

Subscribe

This Post Has 5 Comments

  1. Nice Dany. This has been very annoying and I never really could work out how to protect from the error. This will be a good fix.

    I would love to see this built into a future version of Power Pivot.

  2. Hi Dany,

    The sales for the year of 2007 is 9,791,060 but it read 4,771,584 as last year sales for 2008 and the reason is that all the numbers beyond 20070831 were missing. Why? Could you please check it out for me? Thanks.

    Regards,

    Julian Chen

    1. Hi Dany,

      Please ignore my previous post. I’ve just figured out the reason is that no more sales after 08/31 in year 2008. By the way, I got another question: When I added DateKey from DimDate table in the pivot table rows field, I found the number for the Last Year Sales of 20080229 was duplicated from the Sum of Sales of 20070228 due to year 2008 is a leap year. Was there any measure in the data model to handle this case? Plese kindly advise. Thanks.

      A newbie to DAX

      Julian Chen

      1. One year before of 2/29/2008 is 2/28/2007
        Also the parallel day of 2/28/2008 is 2/28/2007 so you see the same value twice
        Notice the total of last year sales doesn’t double count.
        Dany

Leave a Comment or Question