cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

last time refresh

Hi,

 

Is it possible to add to the report/dashboard the date and time of the last refresh?

 

Many thanks.

 

Nir

2 ACCEPTED SOLUTIONS

Hi,

 

Maybe this will solution will help,

 

http://www.powerpivotpro.com/2010/11/add-a-last-refreshed-date-readout/

 

I will try tommorow and update 🙂

View solution in original post

Hi nir,

 

You can add a calculated column (Last Refresh Column) in the model with the formula =NOW()

And measure Last Refresh Date :=MAX(Table[Last Refresh Column])

 

Since calculated columns are calculated only on model refresh - it will make sure the calc is right

 

 

View solution in original post

60 REPLIES 60
Frequent Visitor

Hi all,

 

Best way to actually do this is to pull in this website using the web conector https://www.timeanddate.com/worldclock/uk/london

 

***edit*** go here https://www.timeanddate.com/worldclock/uk

 

Use a card to display the cities time you would like 🙂

 

far easier than all this coding!

Thank you @trysodin!  Your idea worked perfectly for me.  My previous solution was working but after the recent switch from DST to PST, it was off by an hour.  This is a much simpler solution.  Thank you!

 

let
    Source = Web.BrowserContents("https://www.timeanddate.com/worldclock/usa/san-francisco"),
    #"Extracted Table From Html" = Html.Table(Source, {{"LastRefresh", "[id*=""qlook""]"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Extracted Table From Html","ST","ST; ",Replacer.ReplaceText,{"LastRefresh"})
in
    #"Replaced Value"

Advocate II
Advocate II

This has been explained before in this thread, but I try to do it more precis

 

First create a DummyTable using the following DAX-code:

DummyTable = DATATABLE(
    "Dummy Column"; BOOLEAN; {{true()}}
)

 

Then create a new calculated column in the DummyTable using the following DAX Code:

Last Data Refresh = NOW()

You might also want to add some text, and that could be done using a measure:

Refresh Time Text = 
"Data Last Refreshed: " & 
FORMAT(
  MAX('DummyTable'[Last Data Refresh]); 
  "yyyy-MM-dd dddd hh:mm:ss"
)

 

You can use any table in you dataset and create a new column, but as only one row is needed (and you might need this dummy table for other things), I would create a DummyTable of one row. You can't (at least I don't know a way) use the now() function within the DATATABLE function, so that needs to be done separately as I describe above

Kudo Kingpin
Kudo Kingpin

So, I realize now that the problem is not a time problem but really a custom calendar problem. Have your friendly neighborhood DBA put a DST column on the date table where if would be zero if Standard Time and +1 if daylight savings time. Or do this with a custom calendar in power query. Then add the DST offset to the current date and time. And because it's being handled on the date table, the date would actually different for the full day of starting and ending DST (don't refresh from 12am to 2am).


(DST) in most of the United States starts on the 2nd Sunday in March and ends on the 1st Sunday in November.

No Need - the Powerquery Solution I Provided accurately reporst the local time irrespective of the location where the data was refreshed

@DavidRPitts:

Your approach is brilliant. It uses the US locale to get the time zone shift.


@DavidRPitts wrote:

Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US"),


It worked fine in Power BI desktop, locally, but when I uploaded to the service it didn't work right.

 

I think you're on the right track, but I am not quite there yet.

 

 

 

@DavidRPitts

Unfortunately, I don't believe that is accurate.

In the Eastern Time Zone, we are currently -4, however during DST we are -5, so we would need to modify the "DateTimeZone.FixedUtcNow(),-8" during each season.

 

 

let
    Source = #table(type table[LastRefresh=datetime], {{DateTimeZone.LocalNow()}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetimezone}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US"),
    #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Last Refresh Local", each DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),-8)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Last Refresh Local", type datetimezone}})
in
    #"Changed Type1"

Regular Visitor

My hack for this issue.

 

I am using SQL Database as my source. so I added another table which gets "select getdate() as LastRefreshed" . this is updated everytime the report is refreshed and will have the acurate value in GMT of what you see on last refreshed value on your dataset. making changes accordingly to the timezone solved my problem. 

@DavidRPitts

 

I could not get your solution to work as is. However, this is what I finally got to work:

 

 

let
    StandardLocal = -6, 
    UTC_Time = DateTimeZone.FixedUtcNow(),
    CurrentYear = Date.Year(DateTimeZone.SwitchZone(UTC_Time,StandardLocal)),
    DST_Start = #datetimezone(CurrentYear,3,13,2,0,0,-6,0),
    DST_End = #datetimezone(CurrentYear, 11, 6, 2, 0, 0, -5, 0), 
    AdjustDST = if UTC_Time >= DST_Start and UTC_Time < DST_End then StandardLocal + 1 else StandardLocal, 
    Source = #table(type table[LastRefresh=datetime], {{UTC_Time}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetimezone}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US"),
    #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Last Refresh Local", each DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),AdjustDST), DateTimeZone.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Last Refresh Local", type datetimezone}})
in
    #"Changed Type1"

It uses some variables at the top to define standard local time and the current start and end of daylight savings time.

I also start with UTC so that I get the same results on my machine as in the service.

Helper II
Helper II

Instead of just adding time zone Offsets (which Many suggest) ,

use the Following Power Query

 

let
    Source = #table(type table[LastRefresh=datetime], {{DateTimeZone.LocalNow()}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LastRefresh", type datetimezone}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"LastRefresh", type datetimezone}}, "en-US"),
    #"Added Custom1" = Table.AddColumn(#"Changed Type with Locale", "Last Refresh Local", each DateTimeZone.SwitchZone(DateTimeZone.FixedUtcNow(),-8)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Last Refresh Local", type datetimezone}})
in
    #"Changed Type1"

 

This Give two Dates, base tiime zone of the refresh and the "Local Time" I am in Pacific and just Ran this in Desktop ift Gives teh Following

 

Because we have just "Sprung Forward" to Summer time

 

Local Time.PNG

on Power BI Service it Gives the Following

Service Local Time.PNG

How are you automatically accounting for Daylight Saving Time?

We have removed the time indication and are just showing the date as the lazy solution, or where it doesn't really matter if the time is out by an hour not bothering to fix it.

 

To deal with this across all the dashboards in the future we will

1) Only publish the date where time doesn't matter.

2) Adopt the solution suggested by @phanibhushan and using a SQL database that we have access to.

3) Another alternative is to scrape the time from a webpage that specifies the time in Summer time. Although I don't like the idea of introducing a dependency on an external website in out dashboards.

Helper I
Helper I

Yes. go to top right corner of dashboard and click on three dots> go to edit details > Tick the display last refresh time.

@cjayaneththi: on Reports or just on Dashboards?


@cjayaneththi wrote:

Yes. go to top right corner of dashboard and click on three dots> go to edit details > Tick the display last refresh time.


 

Just on the dashboard. 

Frequent Visitor

o show last refreshed on a dashboard click on the 3 dots on the top right OPEN MENU click on the pencil icon TILE DETAILS then click FUNCTIONALITY Display last refresh time This will be good enough for most people. The problem with the new measure solution is that once you open the desktop app it will immediately show you that time as the last refresh time, even if you havent actually refreshed it from the actual data source.

Hi @forzajoensy the main problem with the tile details is that is shows when the visual display in the tile was last refreshed not the data itself. So you could have the user thinking that the underlying data was refreshed 10 minutes ago when it hadn't actually changed for 3 months.

 

Frequent Visitor

o show last refreshed on a dashboard

 

click on the 3 dots on the top right OPEN MENU

click on the pencil icon TILE DETAILS

then click

 

FUNCTIONALITY

Display last refresh time

 

 

This will be good enough for most people. The problem with the new measure solution is that once you open the desktop app it will  immediately show you that time as the last refresh time, even if you havent actually refreshed it from the actual data source. 
Anonymous
Not applicable

I don't get an Open Menu.  I only get Export Data or Remove. 

Also is there anyway to select the font for this?  I have tried formatting the

category, but that only changes the Last Refresh and not the actual Data and Time.

Hi @Anonymous

 

Which solution are you using ?

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors