Forum Discussion
Database refresh date and power bi refresh date
Does anyone have an idea for a dax measure to find out when our database was last refreshed and when our power bi report was last refreshed. There are times when database gets refreshed but power bi report does not get refreshed due to some reasons such as timeout errors.
hi Pankhuri_16
Step1: You can use GetDate function in SQL Server and store that date only after all necessary tables have refreshed.
Step2: Capture date during Power BI refresh, please note that by default when you refresh your dataset on Power BI service, it returns datetime in UTC, so it may differ from your date time captured on SQL Server. You will need to make sure Date returned by Power BI service is per your timezone by converting it(Check Timezone functions in PowerQuery).
3 Replies
To create a DAX measure to find out when your database was last refreshed and when your Power BI report was last refreshed, you can use the following approach:
Database Refresh Date:
- Assuming your database refresh date is stored in a table in your database, you can create a DAX measure to retrieve the maximum or latest refresh date from that table.
Power BI Report Refresh Date:
- Power BI does not have a built-in feature to directly access the refresh date/time of a report. However, you can use a workaround by creating a datetime variable in Power Query that gets the current date and time when the report is refreshed. Then you can use this variable to track the last refresh time.
- To create a datetime variable in Power Query, you can use the DateTime.LocalNow() function. This function returns the current date and time in the local time zone.
- Once you have this datetime variable, you can store it in a table or use it directly in a DAX measure.
Here's an example of how you can create a DAX measure to find out the last refresh date/time for both the database and the Power BI report:
DatabaseLastRefreshDate = MAX('DatabaseRefreshTable'[RefreshDate])
PowerBILastRefreshDate = MAX('PowerBIRefreshTable'[RefreshDateTime])
Replace 'DatabaseRefreshTable' and 'PowerBIRefreshTable' with the actual names of your tables where you store the refresh date/time information for the database and Power BI report respectively.
Ensure that you have a column in your 'PowerBIRefreshTable' that captures the refresh date and time whenever your report is refreshed. This column can be populated using a datetime variable created in Power Query as mentioned earlier.
With these measures in place, you can easily track when your database and Power BI report were last refreshed and compare them to identify any discrepancies.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
How do we store database refresh date in any table on sql server ?
hi Pankhuri_16
Step1: You can use GetDate function in SQL Server and store that date only after all necessary tables have refreshed.
Step2: Capture date during Power BI refresh, please note that by default when you refresh your dataset on Power BI service, it returns datetime in UTC, so it may differ from your date time captured on SQL Server. You will need to make sure Date returned by Power BI service is per your timezone by converting it(Check Timezone functions in PowerQuery).
