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(
In quite a few cases once you start using such expressions you will see this annoying error message:
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.
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.
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:
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.
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.