“Because there is nothing worse than a stale report.”
OK, so you’ve built some killer models and reports. You’ve published them to SharePoint. You’ve scheduled automatic refresh to run, say, once per week.
You have this PowerPivot thing All. Dialed. In.
But are the report consumers satisfied? Hell no, they aren’t satisfied! If there’s one thing you can be certain of, your report consumers will NEVER be satisfied.
And hey, that’s kinda cool. It’s a good thing. You weren’t given all this new power just so you can sit on your laurels, now were you? Nope. True PowerPivot pros aren’t even sure they can FIND their laurels, much less sit on them. You will always be improving – both your skillset AND the reports you produce.
Today’s improvement: automatically informing the report consumers of how “fresh” the data is. It’s actually pretty simple.
Step One: Add a [LastRefreshed] Measure
Hopefully, somewhere in the PowerPivot window, you have a column whose most recent date is always the date on which the data was last refreshed.
For instance, in a retail system you might be able to use your Sales table for this, as long as there are no “holes” in your data in terms of dates (meaning you always have at least one transaction per day). Or perhaps the Calendar table you pull from the database is always current (and does not contain future dates). Or as a third option, perhaps you can get your db admin to add a single-cell table just for this purpose.
In this example, I’m going to use the TransactionDate column from my Sales table:
Did you know that measures can return dates? They sure can, and it’s killer useful. So let’s create a measure:
[LastRefreshed] = LASTDATE(Sales[TransactionDate])
LASTDATE() is kinda like MAX(), but for dates. So it will always return the most recent transaction date:
Neat huh? A date returned as a measure, in a pivot.
Step Two: Use that measure in a Cube Formula!
I think the most flexible, least intrusive way to display this measure in your report is to create a single [link removed due to 404] cube formula for it, and then stuff that formula into a single cell. That way you have complete control over appearance.
Two things to note:
1) Given that your report will often have columns that are oddly sized to make everything look good, I’ve found it much better to use a single cell formula that includes the label, rather than splitting it across two cells.
2) Cube formulas, when they return a date measure, format it as an integer rather than a date. So you need to reformat it as a date IN THE FORMULA. (You could just use format cells, but since we are putting the label AND the date in a single cell, you can’t just format the cell as a date.)
Long story short: here is your cube formula:
=”Last Refreshed: ” & TEXT(CUBEVALUE(“PowerPivotData”,
“[Measures].[LastRefreshed]”),”mm/dd/yyyy”)
Plop that in the desired cell, and you are all set:
Pretty slick, if I may say so myself.
That cell will refresh every time the workbook is refreshed. But it should not re-evaluate during an update, like when someone operates a slicer.
For more on refresh vs. update and how it can make a HUGE difference in performance, click here.
