Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @irnm8dn,
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:
Hi @irnm8dn,
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:
Great solution. Thanks for sharing this.
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:
Hi,
I follow your given all steps but card not show as date/time format.
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? please tell me how to resolve
I followed my steps exactly as above in a blank PBIX and I've attached it for you to download. Without more details, I can't figure out what's going wrong on your end.
The other option would be for you to share your PBIX, but I'm guessing this would be a security risk.
Thanks! I was able to put it together. I think my steps were slightly out of sequence. Will this "stamp" stay static until the data is refreshed?
Yes - it's treated as a table, so it will refresh with the rest of the report unless told not to.
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 @irnm8dn,
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |