A Simple Trick for Combating “Stale” Slicers

 
Help!  My Power Pivot Slicers Don't Auto Update to the Latest Date on Refresh!

Two Weeks of Refreshes Later…  the Report Still Thinks
Nov 15 is What Everyone Wants to See First!

Your Dashboard is Refreshed, But its Slicers are Stuck in “Yesteryear”

This is a trick I’ve been using forever but never blogged about.  Enough of you are now using Power Pivot for SharePoint (PP Server) that its time has come.  And really, it’s relevant on the desktop too.

On the day you first made this report, you selected the most recent Date (or Week, etc.) in the slicer.  And you saved the report.  All was right with the world!

But then, tomorrow comes.  And all of your slicers still have that “old” date selected, even after you refresh everything.  Ick.  Who wants to go and update all of those slicers to point to the latest date?

I sure don’t.  So, like me, you just let them sit on an old date (or Week, Month, etc.)  This forces the consumers of that report to ALWAYS click the latest date, sometimes after scrolling the slicer to the bottom.  Every time they open the report.  They.  Don’t.  Like.  That.  And neither would you.


A “Shadow” Column for the Slicer Caption!

image

Our New Column is Just a Duplicate of the Original Date Column…
EXCEPT in the Most Recent Row!

Let’s look at that formula…

=IF(Calendar[Date] = MAX([Date]),
                     “Latest”,
                     FORMAT(Calendar[Date],”mm/d/yyyy”)
   )

In English:  Is this row the “latest” row in the table?  If yes, return “Latest.” 

If not, return the original Date value for this row.

Why do we need the FORMAT function?  Why not just use [Date] for the “false” part of the IF?

Because you get this error if you do that:

The second and third arguments of function IF have different data types.  This is not supported.

Since “Latest” is text, we need the Date to also be text.  So I use FORMAT to return a Text version of the date.  Yes I could also have just done [Date] & “” to “coerce” the date to be text.

Now we need to set SortBy Column so that this sorts appropriately in the slicer:

SortBy Column Gives Our Slicer the Proper Sort Order Rather than Alphabetical

Our New Caption Column Should Use the Original Column for Its SortBy Order

To the Slicer Cave!

image

I Replaced the Date Field on the Slicer with the SlicerCaption Field Instead.
”Latest” is the same as 12/2/2012.  For Now.

Ok, so far so good.  But then you refresh the data and now 12/3/2013 appears in your Calendar table…

image

We Picked Up a New Date on Power Pivot Refresh,
and “Latest” Has Now “Marched Forward!”

Now we refresh our pivot…

image

All I Did Was Refresh the Pivot.  “Latest” is Still Selected But Now Means 12/3/2013!

Hands Free, or Damn Near It!

If you’re using SharePoint, generally speaking a scheduled refresh, combined with the above tricks, will just take care of this problem for you.

Ditto if you’re refreshing manually on your desktop, for that matter.

imageWhat if Your Calendar is Not “Trimmed?”

The examples above assume that your Calendar table is being refreshed, and it only contains dates up until the most recent date.

I call that a “trimmed” calendar.

But many of you don’t have that.  Instead, you have calendar tables that go all the way into next year.  I’ve even seen calendars that go to the year 2100.

So what do you do?  No biggie.

Just change your Caption column’s formula to reference the Sales table (or whatever you primary data table is) instead of the Calendar table:

image

In Case of Untrimmed Calendar, Break Glass.  And Then,
Just Reference the Date Column in Your Data Table.

12 Responses to A Simple Trick for Combating “Stale” Slicers

  1. Vincent says:

    Nice, trick! I can use this trick outside of Power Pivot.

  2. Marco Russo says:

    Oh this is a damn smart trick! :)

  3. Don Tyrrell says:

    I like this, but in cases where I use the CUBERANKEDMEMBER formula to return the slicer value and I really want it to show the actual date, not the slicer selection “Latest”. Any suggestions?

    • powerpivotpro says:

      Ooh, good one. I suggest writing a new measure with formula something like MAX(Calendar[Date]). Then use a CUBEVALUE function that references the name of the slicer and uses that new measure. Let me know if that works, I’m rushing this answer since I’m on break at a seminar I am teaching :)

  4. Bryan says:

    Just wanted to point out that the direction of snowfall is controlled by the location of the mouse pointer. Sorry, thought it was cool.

  5. Dude, someone has hacked your site! They’ve defaced it with throwback javascript from 1999 that makes it look like snow is chasing my pointer! Oh wait, you wanted that? Freaky.

  6. Avichal Singh says:

    This is a great tip. This works great with Power View reports that are built off of your model as well. You can create the report and it would automatically show you the current month at all times. We implement this slightly differently. In our Date Table we have a “CurrentMonthOffset” field (we also have CurrentYearOffset, CurrentFiscalYearOffset etc.). It is zero (0) for the current month, -1 for the previous month, -2 for the month before etc. +1 for the next month, +2 for the month after etc.

    This lets us build Power View Reports to automatically show, for example the past 12 months at all times (CurrentMonthOffset between -12 and -1) or the past completed month (CurrentMonthOffset = -1). The field is a little cryptic though, maybe we need to combine these with more descriptive labels (Latest Month, Past Month etc.).

Leave a Reply