Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
irnm8dn
Post Prodigy
Post Prodigy

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

1 ACCEPTED SOLUTION
RMDNA
Solution Sage
Solution Sage

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:

 

 x.PNG

View solution in original post

11 REPLIES 11
RMDNA
Solution Sage
Solution Sage

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:

 

 x.PNG

pikers
Frequent Visitor

Great solution. Thanks for sharing this. 

@RMDNA

 

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?

 

dt.JPG

 

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:

 

s4.PNG

@RMDNA

 

I did this.  Same result.  Perhaps the format of the query needs to be changed?

 

dt.JPG

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

 

dt.JPG

 

 

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.

 

Example PBIX download

@RMDNA

 

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.

@RMDNA

 

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

 

s1.PNG

 

 Step 2:

 

Go to the Advanced Editor

 

s2.PNG

 

Step 3:

 

Paste the code

 

s3.PNG

 

Step 4:

 

You can load the result into your report and use it as a field in a visual

 

x.PNG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.