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)).
- 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.
- 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.
- AllExcept is the workaround.