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
Anonymous
Not applicable

How to add the Last Date Time refresh with two simple DAX functions

Hi all - I have figured out a way to add the automatic Last Date-Time Refresh to local timezone with two simple and straight-forward DAX functions that I'd like to share with you. 

If you have tried to set-up the local time on desktop version but the dashboard always reflects the GTW time whenever auto-refresh occured, it's because the Last Date-Time refers to the last refresh of Power BI server based on GTW. You can follow step by step below and it will work flawlessly no matter which local timezone you are.

 

  1. Create a custom column as UTC time.I use a Brand logo table to create a custom column since the data table is very small. If you pull data from direct source, you should create a new table and custom column by New Source/Blank Query. Why UTC? Because there is no time difference between GWT and UTC. However, GWT is a timezone while UTC is a timestandard. Click here for more details.

 

DAX= Table.AddColumn(#"Renamed Columns", "Last Refresh Date Time", each DateTimeZone.FixedUtcNow(), type datetimezone)

Capture2.JPG

  1. Add one more custom column by DAX switch zone function: I switch from UTC timezone to my local PST timezone by -7 hours. If your local time is different more than PST, you can adjust -/+ hours based on the difference time between UTC and your local timezone. You also add more other timezone by repeating this step 2. 

DAX= Table.AddColumn(#"Added Custom", "PST", each DateTimeZone.SwitchZone([Last Refresh Date Time], -7), type datetimezone)

Capture3.JPG

  1. Use the PST column for the Last Refresh DateTime. This will work on the app as well. 

Capture5.JPG

2 REPLIES 2
Anonymous
Not applicable

This works great! Thank you for this. 

@Anonymous How does this work with Daylight Saving Time? Meaning, when DST ended/began in November/March, did the timezone automatically update to reflect DST, or did you have to manually swtich between -7 and -8 in the formula? 

 

If the latter, is there a way to filter the formula based on when DST actually occurs? 

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.

Top Solution Authors