cancel
Showing results for 
Search instead for 
Did you mean: 
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
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors