skip to Main Content

 
The King of Pivots

“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:

PowerPivot Column Basis for Last Refreshed Measure

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:

PowerPivot Last Refreshed Date In a Pivot

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 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:

PowerPivot Report Home Page with Last Refreshed Date

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

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 15 Comments
  1. not sure if it make sense to comment a 3 year old post but maybe people still need last refresh dates in a report but do not have a column with transaction data in their data tables.

    I created a unrelated single cell table in my model with a calculated column =NOW() . Everytime sharepoint updates the model my NOW column shows the exact time of the update. Readout with cubevalue is the same as described above.

    1. Wouldn’t this change to the following day when accessing the data model even if it wasn’t refreshed, say if you used the measure in a Power View report?

  2. Have entered ‘Last Refreshed’ formula but get error stating that there are duplicate dates, however I modified the measure ‘Last Refreshed’ to MAX instead of LASTDATE and it works within a PivotTable OK, but not in the Cube Formula – advice requested please.

  3. I found that the Last_Data_Update is the UTC time of when the model was last processed. Because our time zone is 7 hours behind UTC, I have to do =[Last_Data_Update]-0.2916. If only there were a way to convert UTC to local time by subtracting an hours component (7) instead of (.2916).

  4. My trusted intern showed me that there is a TIME() function in PowerPivot that can subtract hours. To get my time zone, I can do =[Last_Data_Update]-TIME(7,0,0). I also found out that to display the [Last_Data_Update] from a Tabular model, you have to use slightly different function.

    If connected to a SSAS cube or Tabular, it is:
    =CUBEMEMBER(“Connection”,”[UpdateTime].[LAST_DATA_UPDATE].firstchild”)

    For a PowerPivot, as mentioned in this entry, it is:
    =”TEXT(CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Last Refreshed]”),”mm/dd/yyyy hh:mm”)

  5. Here is a trick – it uses a DMV query and a .odc hack, which also works for adding DAX query tables to your workbooks.

    Step 1.) Create a new connection to your core Power Pivot workbook.
    Step 2.) Remove the connection from your workbook
    Step 3.) Go to Documents -> My Data Sources; open the data connection with notepad
    Step 4.) Change the from MODEL to Query, enter the DMV query in the Query tags as shown below.

    QUERY
    SELECT

    [LAST_DATA_UPDATE]

    FROM

    $system.MDSCHEMA_CUBES

    WHERE

    [CUBE_NAME] = ‘Model’

    Step 5.) Go back into your workbook, click on existing connections and add the connection again. Select ‘Insert Table’.

    Now, you should see a one cell table, that shows the date and time that the data in your Power Pivot core model was last refreshed. This should update whenever you refresh your thin workbook.

    Enjoy!

    1. Would this work once you hosted the file in SharePoint? Seems like the connection would mean the workbook would have to be local. Seems kind of nuts there isn’t an easy way to publish this date assuming you don’t have an actual column in your data that would always have a max of the day of refresh.

  6. Okay. So here’s my question…how do you then display this measure in a Power View report. Viewing the recent demo video of Power Update I can see where Rob added a time stamp measure to the report but I can’t figure out how to do it. This measure simply will not show up in the field listing for me in Power View on SharePoint. Is there some way to fake this?

    1. Great question Mike. I just put the column on the Power View report, not a measure, because as you’ve discovered, Power View doesn’t let you use text or date measures.

      Drag the column onto the report as its own independent report component – don’t make it part of another table or chart.

      (I’ve also edited your question to be “Power View” instead of “Power Pivot.”)

  7. I just bought your book PowerPivot Alchemy and this is the first thing to try in the book. I could not get it to work and a quick Google brought me here.
    It says to create the measure with [LastRefreshed] = but I thought measures were created LastRefreshed:=. When I try your way I do not get a measure.
    When i create the measure my way then put the formula into Excel I get an #N/A.
    If I add the measure to a pivottable values section it works and i get a date.
    Any ideas?

  8. This is really cool. I am wondering if last refresh date can get stored in a separate table to assist in incremental update. Not sure how it is implemented. Can you do a post on that please?

Leave a Comment or Question