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!


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]),

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!


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…


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

Now we refresh our pivot…


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:


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

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology.

This Post Has 13 Comments

  1. Vincent

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

    1. powerpivotpro

      Interesting! Can you elaborate? Non-Power Pivot slicers or something else?

  2. Marco Russo

    Oh this is a damn smart trick! :)

    1. powerpivotpro

      Hey thanks Marco! I think your mastery of CROSSJOIN and SUMMARIZE is smart. I think this is merely Excel-style hackery distilled to 200 proof :)

      1. Marco Russo

        Rob, a smart idea don’t have to be complex to be smart: it’s smarter when it’s simpler!

  3. Don Tyrrell

    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?

    1. powerpivotpro

      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

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

    1. powerpivotpro

      Wow I had NEVER noticed that! (WordPress does this every year in December). That is in fact way cool in my book :)

  5. Colin Michael

    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.

    1. powerpivotpro

      WordPress does it by default this month. I suppose there’s a setting somewhere to disable.

  6. Avichal Singh

    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.).

  7. Steve Orford

    Is there a way of autoupdating slicers baed on SSAS cube data, rather than tabular data?

Leave a Comment or Question