Last Refresh Date in Power BI

[anmolmalviya05]

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

[tweinzapfel]

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]

@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!