Mutually Exclusive slicers?

[schbrownie]

Hi everyone,

I have a somewhat complicated situation that I'm trying to resolve.

My company uses two calendars for reporting purposes. The sales and marketing teams typically rely on the standard calendar. Finance and corporate rely on a 5/4/4 calendar. I have been asked to make my reports (everything in PowerPivot) be able to switch back and forth between a standard calendar and a 5/4/4 calendar. I would like to have a slicer (or something like a slicer) that would allow me to switch back and forth between the two calendars.

The problem I run into with a typical slicer is that the slicers typically act independently of each other and I would like them to be mutually exclusive.

I need to try to stay away from macros (which is the only possible solution I found so far).


Thanks everyone for their help!


p.s. For those not familiar, a 5/4/4 calendar is one where the first week of a quarter has 5 weeks, and the second and third months each have 4 weeks. This creates 4 (fairly) evenly balanced quarters.

 

[scottsen]

"May you live in interesting times" This concept is totally blowing my mind. Cool problem.

I'm actually not totally sure I understand your actual question about slicers being mutually exclusive, but I'm comfortable ignoring that and having a discussion anyway (By default Slicers do react to each other... clicking California will gray out cities that are not in California...)

I assume you will unite the calendars into 1 "common" calendar? Otherwise, its going to be weird building the reports. What would you put on rows? (sometimes you want Months, sometimes you want Period Id)

If you haven't already, I would just google for "powerpivotpro disconnected slicer" and take a look at a few of those. I didn't find a perfect example, but the general idea will still be helpful.

You are going to have a "disconnected slicer" with values "Traditional Calendar" and "5/4/4 Calendar". Then every measure you write is going to have to check that slicer.

[PriorYear-TotalSales] := IF([CalendarSlicerChooser] = 1,
CALCULATE(SUM(Sales), PREVIOUSYEAR(Calendar[DateKey])),
CALCULATE(SUM(Sales), FILTER(ALL(Calendar), Calendar[PeriodId] = MAX(Calendar[PeriodId]) - 52))

If you were thinking along some totally different lines, I will be super curious to hear about it

 

[dhExcel]

You might find this information useful;

Time Patterns – Dax Patterns