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.
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;
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;
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: