Refresh date\time in a Power BI report and dashboard29-01-2019 0 Comment(s)
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.
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:
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.
No Comments to Show