Showing Current Time and Latest Refresh Time at Local Timezones in the Power BI Report

I have previously written a method explaining different ways of getting the local timezone. However, that method was only for a scenario with users all in one timezone. What if your users are in different time zones? what if you want to show them information in the local timezone for each user? In this article, I’ll share a simple trick for that.

Timezone table

If you have users with different timezones, you can add their timezone into the user table. The user table is a table that has their Power BI IDs in it (Power BI ID is their login to the Power BI service or Power BI report server). I have previously explained what a user table is in this article.

An example of a user table with timezone offset looks like below:

The timezone offset is the hours offset from the UTC. for example, -7.5 means UTC -7.5.

This table is a table that you load into Power BI. You can call it Users table, Timezone table or any other names you like.

This table can be related to the other tables (especially if you are implementing a dynamic row-level security), but it can also work perfectly fine without relationship. Because this is not a row-level security implementation.

Local Now Measure

Now that you have the timezone offset of each user, you can easily fetch the timezone offset of the logged-in user, using the UserPrincipalName() function with combination of another function such as LookupValue. Here is an example of how you can get that information;

var _timezoneoffset=LOOKUPVALUE(
'User'[Timezone offset],
'User'[Email],
USERPRINCIPALNAME()
)

In this article I explained how the LookupValue function works.

You can then use the timezone offset in your local time calculation as below;

NOW()+(_timezoneoffset/24)

The whole code is as below;

Local Now =
var _timezoneoffset=LOOKUPVALUE(
'User'[Timezone offset],
'User'[Email],
USERPRINCIPALNAME()
)
return
NOW()+(_timezoneoffset/24)

This code simply will give the local current time for each user’s timezone;

Latest Refresh Time (Locally)

You can use the same approach to show the latest refresh time in the local time zone of the user. If you want to do that, you need to use the Power Query DateTime.LocalNow() function, and load it into a table.

Then use that in the DAX expression below instead of the NOW() function.

Last Refresh Time Local =
var _LastRefreshTimeServer = MAX('Last Refresh Time'[Last Refresh Time])
var _timezoneoffset=LOOKUPVALUE(
'User'[Timezone offset],
'User'[Email],
USERPRINCIPALNAME()
)
return
_LastRefreshTimeServer+(_timezoneoffset/24)

This will return the local refresh time in the timezone of the user;

Considerations

This method works fine as long as you have up-to-date data in the user table. If a user is missing, you need to consider a default value (which can be done with a small change in the DAX code above). If a user moves from one location to another location with a different timezone, this implementation won’t recognize that change. You have to keep the user table up-to-date manually. Unless you write an application that sends their location’s timezone into this user table. This Might be possible with Power Apps.

Download Sample Power BI File

Download the sample Power BI report here:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.