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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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;

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;

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.

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:

Enter Your Email to download the file (required)

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

2 thoughts on “Showing Current Time and Latest Refresh Time at Local Timezones in the Power BI Report

Leave a Reply

%d bloggers like this: