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]),
“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:
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:
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.
What 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.
