Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Pankhuri_16
Frequent Visitor

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. 

1 ACCEPTED SOLUTION

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). 

View solution in original post

3 REPLIES 3
Pankhuri_16
Frequent Visitor

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). 

123abc
Community Champion
Community Champion

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:

  1. 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.
  2. 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.

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors