Forum Discussion
Modify Timezone for apps own data power bi embedded model
THank you for the very detailed response. It has been extremely helpful but I believe I am stuck. In your response you mention that I need to enable my browser's timezone Conversion.
I have taken the following steps with my import model.
I convert my EST value to UTC:
DateTimeZone.SwitchZone(DateTimeZone.From([#"Creation DateTime - Timezone"]), 0)
I then create a new column and have it converted to local time
DateTimeZone.ToLocal(DateTimeZone.From([UTC]))
I upload that to the service and check. It looks like it converts the local back to EST just fine. So I change the timezone on my laptop to PST and try again. The Timezone stays as EST in the service. I log out of the service close the browser and come back in. Still EST. Am I missing something? I even logged in via incognito on chrome and still had EST for Local. Let me know what I missed...
Below is what I'm seeing. My laptop's timezone is set to PST.
My Testing Table (Import).
let
Source = Sql.Database(Server, Database, [Query="SELECT [Company_ID]#(lf) ,[Ticket_ID]#(lf) ,[Create_DateTime]#(lf)#(tab) , ([Create_DateTime] AT TIME ZONE 'Eastern Standard Time') AT TIME ZONE 'UTC' AS UTCDateTime#(lf) FROM [Analysis].[FACT]#(lf)#(lf)", MultiSubnetFailover=true]),
#"Duplicated Column" = Table.DuplicateColumn(Source, "UTCDateTime", "UTCDateTime - Copy"),
#"Changed Type" = Table.TransformColumnTypes(#"Duplicated Column",{{"UTCDateTime - Copy", type datetimezone}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "UTC_Local", each DateTimeZone.ToLocal(DateTimeZone.From([#"UTCDateTime - Copy"]))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "EST", each DateTimeZone.SwitchZone([UTCDateTime], -4)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "PST", each DateTimeZone.SwitchZone([UTCDateTime], -7))
in
#"Added Custom2"
What I'm seeing (I expect UTC_Local to be PST not EST).
Hi Don-Bot ,
If you need the UTC_Local column to reflect the local time zone of the device or a specific time zone, here are your options:
Option 1: Explicit Time Zone Conversion
Instead of relying on DateTimeZone.ToLocal, explicitly set the desired time zone conversion using DateTimeZone.SwitchZone. For example:
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "PST", each DateTimeZone.SwitchZone([UTCDateTime], -7))
Added the (-7) for switch to PST, This ensures that the PST conversion is consistent regardless of where the report is run.
// I often use this method to get the Local refresh time on service
Else you can parameterze the number as follow,
Option 2: Parameterize Time Zone Offsets
Create a parameter for the desired time zone offset and use it in DateTimeZone.SwitchZone. This allows you to dynamically adjust the time zone without modifying the code.
Steps:
- Create a parameter in Power Query for the offset (e.g., TimeZoneOffset).
- Use the parameter in your SwitchZone formula:
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Dynamic_Local", each DateTimeZone.SwitchZone([UTCDateTime], TimeZoneOffset))
Thank you again SacheeTh ,
Let me know if I'm understanding you correctly. But from what I see is the only way to setup different timezones in my model to pre-configure them with extra columns such as "CST, PST, EST"?I've been able to get the parameter stuff to work but the problem is one model can have users across different timezones. So I can't hard code a parameter into it. The closest thing I could probably do is add the columns for each timezone. Since it appears it's not auto detecting or changing timezones with the methods I've tried above.
Hi Don-Bot ,
Yes, your understanding is correct. Unfortunately & as far as I know, Power BI does not natively auto-detect or dynamically adjust time zones for individual users in the Power BI Service on the report end. If your model has users across multiple time zones, pre-configuring time zones with extra columns (e.g., "CST", "PST", "EST") is one of the most straightforward approaches.That's the only this that pop to me right now, other than user wise RLS or OLS.
Implement Row-Level Security (RLS)
If each user's time zone can be determined from their identity (e.g., region in a database), you can implement Row-Level Security to filter or adjust time zones dynamically based on user roles.
Ah on the 2nd through, you can set up a DAX for that too, I know you also have came a cross on this for sure. Anyway I'll add the steps here. 😄
Use DAX for Dynamic Calculations
If you need user-specific flexibility, consider calculating offsets in DAX. While this won't detect the user's system time zone, you can provide a slicer or control for users to select their time zone.
- Create a Time Zone Table:
- A simple table mapping time zones to that has all the offsets:
Time Zone | Offset EST -5 CST -6 MST -7 PST -8
- A simple table mapping time zones to that has all the offsets:
- Create a Slicer:
- Add a slicer to the report for users to select their time zone.
- Create a DAX Measure:
- Use the selected offset to calculate the adjusted time:
AdjustedTime = VAR SelectedOffset = SELECTEDVALUE(TimeZone[Offset], 0) RETURN UTCDateTime + (SelectedOffset / 24)- Display this adjusted time in visuals.
- Create a Time Zone Table: