Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
Is it possible to add to the report/dashboard the date and time of the last refresh?
Many thanks.
Nir
Solved! Go to Solution.
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 🙂
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
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"
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
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
@Anonymous:
Your approach is brilliant. It uses the US locale to get the time zone shift.
@Anonymous 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.
@Anonymous
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"
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.
@Anonymous
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.
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
on Power BI Service it Gives the Following
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.
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.
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.
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
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
20 | |
19 | |
18 | |
9 |