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.