Show the refresh date\time in a Power BI report and dashboard

A short post today. Someone recently  asked me: “How can I show the date and time this data has been refreshed last in my report and dashboard?”.  Here are 2 simple tricks to help you visualize this.

Show last refresh date\time

To show the date and time of the refresh we need to store the date and time of the time refresh runs in the model itself so we can show it in a visual.

One of the easiest options is to use Power Query to store the current date\time as a table. I start in Power BI desktop with “Get data”,  “Blank Query”. Here I enter “= DateTime.LocalNow()”

Next I click “To Table” so it will return a table we can import into the model. Then I can work on it just like I would any table. In my case I would like to have the option to show the date, the time and both together so I need 3 columns. As usual this is very easy with Power Query, I can just use the UI. 

After loading the data to the model I can add the visual to the report:

Now every time the model gets refreshed this datetime gets updated to show you the current datetime (as in datetime of the refresh).   The date time that it picks will be the Power BI server time and timezone when you use scheduled refresh or gateway server date time when using a GW.  If you want to make sure you get the right time here you should convert it to a particular timezone using the DateTimeZone functions

You don’t need to do anything after this to keep it up to date.

Show last order date from the model

Now there is another variation of this where you don’t want to show the last date it was refreshed. Instead you want to show how current the report is based on when the last data was added to the fact table in the model. In this case we cannot use the actual date time but have to look at the dates in the model. 

Let’s take regular adventureworks, here we have Sales with a corresponding order date. I want to show the last order date on my report so we know the last time I have data for. 

One of the easiest ways is to just write a measure that gets the last date I have sales. Something like this:

Lastdatetime =
CALCULATE ( MAX ( FactInternetSales[OrderDate] )ALL ( FactInternetSales ) )

This will get the last date from the fact table where we have sales to show in the report. It will show the date as it stored in the model, no timezone conversions.

All you now have to do is pin this to a dashboard to get a tile that shows you the dates there. That’s it, 2 simple techniques to show relevant dates. 

11 Replies to “Show the refresh date\time in a Power BI report and dashboard

    1. That would only work if you do not have any slicers or filters on the dashboard, the calculate ALL makes sure it always returns the total.

  1. Great article. I am having the issue you mentioned about it not showing the correct time when publishing it to PowerBI on the web (I am using a on-premise data gateway with scheduled refreshes) and having to use the DateTimeZone functions. Right now my time always shows 5 hours ahead (I am in EST zone). I have never used that function. Which function do I need to use to correct the timezone issue? Thanks.

    1. You can do something like this:
      let
      Source = DateTime.AddZone(DateTime.LocalNow(),5),
      #”Calculated Local Time” = DateTime.From(Source),
      #”Converted to Table” = #table(1, {{#”Calculated Local Time”}})
      in
      #”Converted to Table”

  2. This is so very useful because all other solutions I’ve used had there flaws and this one does not plus it is also easy to do!

    Other solutions would have problems with show todays date but I didn’t actually refresh even though I may have published. Or I’d use last date +1 from data but that assumes a transaction yesterday and also doesn’t fix the fake year 2099 fake order date hiding in the data, etc.

  3. Hi

    I wanna see the data between two dates. But with time.(17.02.2018 11:10:30.000 19.02.2018 22:00:55.000). But the costumer must chose. At first i could see all datetimes. Then she/he could chose.

    I couldnt do this. I have been dealing with it for 4 days.

    Can u help me?

    I’d be very happy if you could help

  4. I wonder if its possible to create measure which tests the last update of pbix file itself.

    It would be great to be able to write down when was the last time design was changed.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.