Use slicers value into DAx for Dynamic filtering

Question

I want to link my Slicers values with DAX expression. What I want is If I select any month & Year from Month & Year Slicer

e.g (March Month & 2013 Year) then it should show table as:

              March - May2011    |    March-May2012       | March-May2013

---

Sum Of Games      200            |          300           |         400

Sum Of Assets     150            |          400           |        600

Basically it should calculate values from selected month to (Selected month +3) & selected year to (Selected Year - 3).

So is it possible using Slicer selection dynamically?

I have done this in static way. For reference you can see this link

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/73735550-2a8c-4270-b09c-70c98aec5eb8/grouping-based-on-month-range?forum=sqlkjpowerpivotforexcel

but I want it based on user selection dynamically.

Thanks in Advance!

Answer 1

Rameshwar,

without knowing your table structure, it's a bit difficult to propose "the right" solution.

However, to keep things simple (and speedy), I suggest using OLAP Pivot Table Extension. It allows you to grab selected values on slicer and put them into a regular cell.

This way, you could add just year as columns header, and list the months above them. See this article for more details, it describes a similar scenario:

I am not a big fan of cascaded IFs and using complex SWITCH expressions. They are fine when dealing with small amount of data, but could lead to many difficulties and I would suggest avoiding them whenever possible.

Hope this helps :)