Power Bi Dynamic Time Zone Transformation

[Musab-Ali]

How can we achieve the following requirements in Power BI?

We have user data along with their information, and all the Power BI visualizations are built using this data.

In the user table, there is a column named User_Joined_Date, which is in the format:
YYYY-MM-DD HH:MM:SS +00 UTC
This column is currently in the UTC time zone, and the entire dashboard is based on this timestamp.

Now, we want to implement a slicer or dropdown that allows the user to select multiple time zones like:

PST (+5)

EST (-4)

BST (+1)

GST (+4), etc.

Whenever a timezone is selected, the data should dynamically convert the User_Joined_Date from UTC to the selected timezone. All visuals should reflect the converted time accordingly.

We’ve also added a third-party date picker which is connected as follows:
Date Picker’s Date column → related to → User table’s User_Joined_Date column.

The key point is that we are also splitting the User_Joined_Date column in Power Query into two separate columns:
User_Joined_Date (date only) and User_Joined_Time (time only),
in order to establish the correct relationship with the date picker.

[v-tejrama]

Hi @Musab-Ali,

Thanks for reaching out to the Microsoft fabric community forum.

To display the user count by local join date based on the selected time zone in Power BI, here’s the approach that works perfectly:

First, create a Date table using the CALENDAR() function and mark it as a Date table. Then, create a TimeZones table with two columns: TimeZoneName (e.g., PST, EST, IST) and OffsetHours (the UTC offset for each time zone).

Next, add a slicer to the report using the TimeZones[TimeZoneName] field, so users can select the desired time zone dynamically.

To calculate the user count adjusted to the selected time zone, use this DAX measure:

User Count by LocalDate =
VAR Offset = SELECTEDVALUE(TimeZones[OffsetHours], 0)
RETURN
CALCULATE(
COUNTROWS(Users),
ADDCOLUMNS(
Users,
"LocalDate", DATEVALUE(Users[User_Joined_DateTime_UTC] + Offset / 24)
),
TREATAS(VALUES('Date'[Date]), [LocalDate])
)

Finally, use Date[Date] (or Day, Month, etc.) from your Date table in your visuals, along with this measure. The result will show the user count by local join date, dynamically adjusting based on the selected time zone from the slicer. Simple, effective, and works great with multiple time zones.

Please find the attached pbix file for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it.

Best Regards,
Tejaswi.
Community Support

[johnt75]

One option would be to create a column for each the date in each timezone you want to cater for.

Before you split the datetime into separate columns you could add a new column using the DateTimeZone.SwitchZone function and then keeping just the date part.

Create inactive relationships from the Date table to each timezone date column and then create a calculation group, with calculation items for each timezone. Each item would have the same format but use the appropriate relationship for that timezone, e.g.

PST =
CALCULATE (
    SELECTEDMEASURE (),
    USERELATIONSHIP ( 'Date'[Date], Users[PST Date] )
)

By putting the calculation items into the slicer the user will be able to select which one they want to see.

You would probably want to hide all the date columns in report view, and always use columns from the Date table in any visuals so that the correct date is displayed.