Forum Discussion

Don-Bot's avatar
Don-Bot
Helper V
1 year ago

Modify Timezone for apps own data power bi embedded model

I have a apps own data power bi embedded semantic model setup.  The model itself is an import model that is setup as a star schema.  Multiple clients and multiple users across different timezones uti...
Need Help
SacheeTh's avatar
SacheeTh
Resolver II
1 year ago

You're absolutely right! The standard and efficient approach to handle time zones in Power BI, especially in multi-user, multi-time-zone scenarios, is to store all datetime values in UTC and let the report user's browser handle the time zone conversion.

Don-Bot, Here's how you can implement this approach:

Steps to Use UTC Datetime with Browser-Based Time Zone Conversion

  1. Store All Datetime Values in UTC:

    • Ensure your data source stores all datetime values in UTC format.
    • If your data isn't already in UTC, convert it during ETL using your database engine, ETL tool, or Power BI Dataflows.

  2. Enable Browser Time Zone Conversion:

    • Power BI automatically converts datetime fields from UTC to the user's local timezone in the report visuals based on the user's browser settings.

    Note: This conversion happens automatically for datetime columns in reports. However, calculated columns and measures often do not get this treatment unless explicitly configured.

  3. Avoid Manual Adjustments in the Model:

    • Do not apply manual time zone adjustments in DAX or your model. Keep datetime fields in UTC to avoid duplicating time zone logic.

  4. Handle Fixed Offsets (Optional):

    • For reports requiring time zone offsets (e.g., client-specific reports with fixed time zones), you can create a parameterized time zone offset or a Time Zone Dimension Table.

Example Implementation

Adjust ETL to Store UTC

  • If your source data contains local datetime values, convert them to UTC during the data load:

    • SQL Server:

      SELECT CONVERT(datetime, SWITCHOFFSET(CAST(LocalDateTime AS datetimeoffset), DATENAME(TzOffset, SYSDATETIMEOFFSET())))
FROM YourTable;

    • Power Query: Use DateTimeZone.ToUtc([LocalDateTime]).

Use Native Browser Conversion

Once datetime values are stored in UTC, ensure the column type in Power BI is set to DateTime. For example:

  • Table: Ticket
  • Column: Creation DateTime (stored in UTC)

Add this column directly to your visuals, and Power BI will automatically translate it to the local timezone based on the user's browser.

Example for Fixed Offset (If Needed)

If specific users or clients require reports in a fixed time zone:

  1. Create a Time Zone Offset Table:

    • Example:TimeZone Offset (Hours)
      UTC0
      EST-5
      PST-8

  2. Add a Calculated Column or Measure:

    AdjustedDateTime =
Ticket[Creation DateTime] + SELECTEDVALUE(TimeZoneOffsets[Offset]) / 24

  3. Use the Adjusted Datetime in Visuals:

    • This is necessary only if you must display fixed time zones, not browser-based local time zones.

Advantages of This Approach:

  1. Simplifies the Model:

    • No need to store multiple adjusted datetime values or calculate them dynamically.
    • No resource strain from real-time DAX adjustments.

  2. Automatic Time Zone Detection:

    • End-users see times based on their browser settings without extra configuration.

  3. Scalability:

    • Suitable for large datasets and multi-tenant scenarios.

Let me know if you'd like detailed guidance on any of these steps!

Don-Bot's avatar
Don-Bot
Helper V
1 year ago

Hi SacheeTh  and lbendlin ,

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).

 

  • SacheeTh's avatar
    SacheeTh
    Resolver II
    1 year ago

    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:

    1. Create a parameter in Power Query for the offset (e.g., TimeZoneOffset).
    2. Use the parameter in your SwitchZone formula:
       
       

     

    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Dynamic_Local", each DateTimeZone.SwitchZone([UTCDateTime], TimeZoneOffset))

     

    • Don-Bot's avatar
      Don-Bot
      Helper V
      1 year ago

      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.

      • SacheeTh's avatar
        SacheeTh
        Resolver II
        1 year ago

        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.