Convert Timezone based on User Selection (PowerBI.com)

[BHinote]

I have been using Power BI for a couple of months now and have run into the same issue as many other people, regarding the issue of showing Date Time fields relative to the end user's Time Zone.  I have tried all the tricks currently published, that work when running from the Power BI Desktop but fail when running from the Service as everything appears to be based on the Service Machine as apposed to the Client's Machine.

With that in mind and until Power BI has a solution that can determine this based on the Clients Machine, IP, etc. I thought I would provide a manual workaround to allow the users to select their timezone from a predefined list and then use their selection to convert the Date/Time fields.

The problem is that I do not know exactly how to pull this off since I believe this would have to be done in a measure, which expects to produce results across multiple rows rather than each row like a calculated column does.  The other issue is that my current logic uses DateTimeZone.SwitchZone, which I believe only works at the Power Query level and only when the data is loaded rather than at the browser level.

Does anyone have any suggestions on how I might be able to provide a Slicer or Equivalent Visual, with a basic list of Timezones relative to those users who will be running the report, and when the user makes a selection use this to convert the UTC Time to their selected timezone?  This is the only way I can see to address the issue when running the report from Powerbi.com since there does not appear to be a way to pull timezone information based on the Users Machine.

Thank you for any helpful suggestions that may be provided.

[GilbertQ]

Hi there

What I would do, is to create a disconnected table which would have the different hours for the different time zones.

I would then take the value being selected from the user and then this would change the measure for the time for the user.

This would then reflect the time in their time zone. Along with the persistent filters, every time the user logged in it would remember and show them the data in their timezone.

[BHinote]

Thank you GilbertQ for responding to my post.

I understand the principle of creating a Disconnected Table that would contain the various Timezones available for the user to Select and then I would use a Slicer based on the Disconnected Table allowing the user to make a selection.  I then assume that I would need to create a New Measure using the SelectedValue() and Switch() functions to determine what value was selected in the Slicer.

However, what I do not understand is how I can use a Measure to convert the Date/Time fields on a record by record bases.   To my knowledge Measures expect to generate totals across multiple records at the time the Visual is being loaded, whereas Calculated Columns perform calculations at the Record Level but only during the data refresh which occurs prior to the visuals being loaded or the user making a selection.

At the Dax Level I can create a Calculated Column for every timezone supported using code similar to the following:

DateTimeField_PST = MyTable[DateTimeField]  + ((1/24)*-8)      (This is for Pacific Standard Time)

The issue with this is that I would have to have Calculated Columns for every possible timezone I was supporting(i.e. 8 of them currently), but even then I am not aware of a way to use the Switch() function to consume the 8 possible columns, as the Switch function appears to be expecting to use a measure field or to perform measure functionality across multiple records.

Am I missing something that would allow me to create a measure that could do something like the following: (The below example is a Calculated Column, which does not recalculate when the user makes a selection but I do not know of a way to do the same thing as a measure or achieve the same result using a measure.)

DateTimeField_LocalTime =
     VAR ValueSelected = SELECTEDVALUE('Time Zones'[Time Zone], "PST")
     VAR Offset =
          CALCULATE (
          FIRSTNONBLANK ( 'Time Zones'[Offset], TRUE() ),
               FILTER ( ALL ( 'Time Zones' ), 'Time Zones'[Time Zone] = ValueSelected)
          )
RETURN
     MyTable[DateTimeField] + ((1/24)*Offset)

Thank you

[PBILover]

Hi did you got any solution on this? can you please let me know , i am facing similar situation at my end.

[BHinote]

Unfortunately No.  I was forced to create separate reports for the various timezones needed, as there does not appear to be a solution for this.

I do not know why the Power BI Team has not addressed this issue, as everything I have found converts the UTC Time and Zone based on the Servers Timezone and not the User's Timezone.  Considering that most National and/or International Businesses will have users and/or clients that work from timezones other than where the data is served it does not make sense that there would not be a solution to address this issue.  Maybe I am wrong, but at a minimum couldn't they just provide an option in the User's Profile that allows the user to set the Timezone they work from and then allow the Date/Times to be converted base on that...   I do not know, but this is a major problem.. 

[GilbertQ]

Hi there

You are indeed correct, I was thinking of only displaying a single DateTime value and not for all of the reports.

I currently think unless you had to create the seperate columns and try and manage it that way it would be rather a challenge.

What I would do for my end users is that I would explain that the DateTime of the data is where the data is born. That will ensure that the data is always valid on the time based on where it came from.