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.
For last order date, can’t you just use LASTDATE(OrderTable[Orderdate])?
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.
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.
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”
Your comment inspired me to post a small supplement to Kasper’s post on how to solve this while also taking daylight savings into account.
https://selfservedbi.com/2018/12/15/converting-from-utc-to-local-timezone-in-power-bi-and-azure-analysis-services/
Thanks James, great addition!
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.
An easy solution is to add an extra field in the source SQL, something like GETDATE() AS LastRefresh and use this field in the report.
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
good stuff.
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.