Forum Discussion
Last Refresh Date - Alternative Solution
I am currentyl using the following DAX sattement to calculate a time stamp for Last Refresh:
= DateTime.Time(DateTime.LocalNow()) & DateTime.Date(DateTime.LocalNow())
Increasingly, I need a solution that will be in EST but also account for daylight savings time (without manually changing twice a year). I am looking for the best possible solution to convey to the user the recency of the data they are veviewing.
Thanks
Hi Anonymous,
I've found this to be useful when I'm designing reports for worldwide customers. Rather than dealing with general timezones and DST/etc., just pull data related to the location itself.
Query:
let
Source = Web.Page(Web.Contents("http://localtimes.info/Asia/Saudi_Arabia/Riyadh/")),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
date = #"Changed Type"{1}[Column2],
time=#"Changed Type"{0}[Column2],
datetime=DateTime.FromText(date&" "&time)
in
datetime
Output example:
11 Replies
Hi Anonymous,
I've found this to be useful when I'm designing reports for worldwide customers. Rather than dealing with general timezones and DST/etc., just pull data related to the location itself.
Query:
let
Source = Web.Page(Web.Contents("http://localtimes.info/Asia/Saudi_Arabia/Riyadh/")),
Data1 = Source{1}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data1,{{"Column1", type text}, {"Column2", type text}}),
date = #"Changed Type"{1}[Column2],
time=#"Changed Type"{0}[Column2],
datetime=DateTime.FromText(date&" "&time)
in
datetime
Output example:
Thank you. I am not not sure, based on your description, that this will offer the results I am looking for. Perhaps I don't understand how to apply the solution.
Hi Anonymous,
Rather than using a DAX query, run the code above using "Advanced Editor" inside the Query Editor as a blank query.
What it's doing is, rather than pulling the date/time from your PC's internal clock, gets it from whatever site you use in the query and loads it into a table, which stays static until it's refreshed.
Step 1:
Enter the Query Editor and create a new blank query
Step 2:
Go to the Advanced Editor
Step 3:
Paste the code
Step 4:
You can load the result into your report and use it as a field in a visual
Tell me what I am doing wrong.
1. Select Edit Queries
2. New Source
3. Blank Query
4. Advanced Editor
5. Cut/Paste Query as written in the note
And then the card just displays the query?
No, the card will display the data pulled from the code I gave you, which is the date/time info for whatever city is chosen. See below.
So the code I gave was for Riyadh, in Saudi Arabia, so you'll need to change the URL to whatever city is appropriate for you. After that, follow the steps you've listed and you should see the date/time as a new query in the Query Editor.
After that, if you close and apply the changes, the query will show up in your usual Field pane, and the value can be added to a card:
Great solution. Thanks for sharing this.
