Blog Post

Power BI Community Blog
1 MIN READ

Last Refresh Date in Power BI

anmolmalviya05's avatar
anmolmalviya05
Icon for Super User rankSuper User
1 year ago

Steps to Add Last Refreshed Date

Step 1: Open Power BI Desktop

Launch Power BI Desktop and click on the Transform Data option.

 

Step 2: Create a Blank Query

From the Home ribbon, click on New Source and select Blank Query.

 

Step 3: Rename the Query

Once the Blank Query table (named "Query1") appears under the Queries section, right-click on it and rename it to Last Refreshed Date.

 

Step 4: Add M Code in Advanced Editor

Now, open the Advanced Editor from the Home ribbon and paste the following M code:

 

M Code:

let
    Source = #table(type table[Date Last Refreshed=datetime], {{DateTime.LocalNow()}})
in
    Source

Once you paste the code, click on Done.

 

This M code creates a column named Last Refreshed Date, containing the current date and time. The date will update every time you refresh the dataset.

Step 5: Apply Changes

Click on Close & Apply to save and apply the changes.

Step 6: Display Last Refreshed Date in the Report

To show the last refreshed date, you can use either:

  • A Card visualization

  • A Text Box, depending on the available space in your report.

     

Conclusion

Adding a last refreshed date detail in your Power BI report helps users understand data freshness and ensures that automatic refreshes are working as expected.

I hope you found this blog helpful! 🚀

 

Best Regards

Anmol Malviya 

Sr. Data Analyst | Addend Analytics

Published 1 year ago
Version 1.0
how to
tips & tricks
anmolmalviya05's avatar
Icon for Super User rankSuper User
Joined February 20, 2024
View Profile
Power BI Community Blog
On this blog you will find articles, guides and information created by our community members and, from time to time, roundups and community news. If you are interested in becoming a community blog author, send a private message to Natalie_iTalent. An overview of the community blogs can be found here.

3 Comments

  • tweinzapfel's avatar
    tweinzapfel
    Icon for Advocate II rankAdvocate II
    1 year ago

    One thing to be aware of with using the DateTime.LocalNow function is that it can give different results depending on things.

     

    For reports you are testing in Power BI desktop, it'll work great as it's pulling the date/time from your computer.  However, once you publish the report, the date/time is pulled from the server and not your location.  If the server is in a different time zone you will get a different result.  I discovered this when my report was showing it was being refreshed about 4 hours ahead of the my time.

     

    However, you can adjust the code to account for this.  Here is code I have used.  I wish I could give credit as to who created it as I found it off the web.  And, it would can be tailored to account for Daily Savings Time (and the section may have to be updated as well)
    ***************

    let
     
    date = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-4,0)),
    time = DateTime.Time(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),-4,0)),
    //negative four (-4) is based off of EST adjust this on date and time to your timezone.
     
    firstSundayOfNovember = Date.StartOfWeek(#date(Date.Year(date), 11, 7), Day.Sunday),
    SecondSundayOfMarch = Date.StartOfWeek(#date(Date.Year(date), 3, 14), Day.Sunday),
     
    isSummerTime = (date = SecondSundayOfMarch and time >= #time(1,0,0))
            or
    (date > SecondSundayOfMarch and date < firstSundayOfNovember) 
    or 
    (date = firstSundayOfNovember and time >= #time(1,0,0)),
     
    timeZone = (Number.From(isSummerTime))*1 - 1, 
    //negative one (-1) may need to be adjusted depending on your timezone. 
     
    ltime = 
                DateTime.From(date) 
                + #duration(0,Time.Hour(time),Time.Minute(time),Time.Second(time))  
                + #duration(0, timeZone, 0, 0)
     
    in
     
    ltime

     

  • KTOKI's avatar
    KTOKI
    New Member
    1 year ago

    tweinzapfel—Thank you so much! I was struggling, and searching high and low for a simple solution. I'm working in Hawaii, but our Power BI tenant is in Illinois, and Hawaii doesn't have daylight savings time. The only adjustment I needed to make was UtcNow(),-10. Whew!

  • Dionah's avatar
    Dionah
    Icon for Helper II rankHelper II
    11 months ago

    The limit of this method is when you or your user refresh the navigator, it changes. 
    I mean for instance, you planed to put the refresh for instane eveyy 1 hour let's say at 7h AM, 8AM, and 9 AM

     

    The user consults the report at 2 PM and refresh, in this case, the last refresh date is 2PM, it is not the cas. 

    I explored a vue using this SQL in M code 
    SELECT LAST_DATA_UPDATE FROM $SYSTEM_MDSCHEMA_CUBES

    The limits of this one is : if your workspace is not premium, so it's not working and also, there will be some delta between the true last refresh succed and this LAST_DATA_UPDATE FROM

    **** The geat solution****

    Data scrapping 

     

    in the workspace, in parameter and in refesh history, there are some metadata so you just take the last one where the status is succed. 

     

    To do this, 

    -1 using python to write script, and call it in your M code (manual)

    -2 using python script and make an azure function (100% cloud) and call it in M code

    3- Power Automate, this is one ot the best solution  I think