skip to Main Content

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

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 14 Comments
    1. 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. 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. 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 🙂

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

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

Your email address will not be published. Required fields are marked *