Most valuable professional
  • Excel Book Excerpt

Excel Unfilter Using Time Intelligence

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Unfilter Using Time Intelligence

The previous topic showed how you could unapply the Sector filter to get all sales for another sector. What if you need to compare sales for this date to all dates in the month? Or a running MTD number? Or sales from a prior year? DAX introduces many new time intelligence functions that can be used to unapply a filter.

Gotcha: There is a bug in the logic for using calculate with time intelligence functions.

If you wanted to compare calculate Month to Date sales, you should be able to refilter the date using calculate: =CALCULATE(Sum(Sales[Revenue]),DATESMTD(Sales[Date])). The DATESMTD() function returns a list of dates to consider. This wasn’t working in the PowerPivot beta. It was supposed to be fixed in the final product, but it is not. The workaround is to add another filter with ALL(table containing dates). In this case, the formula becomes =CALCULATE(Sum(Sales[Revenue]),DATESMTD(Sales[Date]),ALL(Sales)).

LE10001011.jpg 

Figure 977 To make the formula work, use ALL(Sales).

The problem is that using ALL(Sales) will override all filters on the sales table. In the figure above, a new slicer filters the pivot table to only one product. The All(Sales) filter in the DAX measure overrides the product filter.

LE10001012.jpg 

Figure 978 Using ALL(Sales) overrides the product slicer.

The workaround is to override all of the filters except the product filter:

=CALCULATE(Sum(Sales[Revenue]),DATESMTD(Sales[Date]),AllExcept(Sales,Sales[Product])).

The unfortunate by-product here is that the formula had to be rewritten just because a slicer was added. You would have to remember to rewrite the formula if someone adds a column field, a row field, or any slicer.

LE10001013.jpg 

Figure 979 AllExcept is the workaround.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: