Need help with the UTC time conversion when taking data from SharePoint

[Mike282]

So I'm still a bit frustrated with how Power BI handles UTC time conversions on Power BI Service. I've tried multiple methods short of applying the time offset manually via DAX based off our location. 

My question is whether it's possible to have the UTC time display on Power BI Service to automatically apply the time offset based on the location of the person who's viewing the Power BI report on Power BI service. I know you can add the datetimezone transformation to automatically aply the offset on Power BI desktop based on the location of the machine that has opened the report but is it possible to do it in Power BI service?

One thing I did try on one of the date time columns was to:

1. Change the type to Date/Time/Timezone

2. This then showed the UTC timezone offset against the date/time field

3. I then created a custom column in the Power Query Editor using the column in my previous step and apply a tolocal offset

4. This then applied my timezone offset to the previous column's UTC timezone offset.

5. I then changed the new column data type to "Date" and then applied the changes. The time offset worked perfectly in Desktop and when I published to the BI Service it worked during the initial publish but when the data is refreshed, it reverted back to UTC.

I've been trawling through the forums on anyway around it and it looks like the only way is to apply a manual timezone offset using DAX. What I want to do however is apply the offset based on who's viewing the report has we do have staff in different countries so applying 1 country's timezone won't work.

EDIT: 

I'm just thinking. Is it possible as an alternative to create a disassociated table with the timezones and the offsets of the different countries and include it in a slicer which users can select their own timezones and it'll apply to the value.

I'm not sure if this works on calculated columns however.

Any help would be much appreciated.

[v-yulgu-msft · Accepted solution]

Hi @Mike282 ,

I'm just thinking. Is it possible as an alternative to create a disassociated table with the timezones and the offsets of the different countries and include it in a slicer which users can select their own timezones and it'll apply to the value.

---

It might be possible to achieve this via adding time offset to UTC in DAX formula. As you mentioned, you need an extra table to list the mapping relationship between each country and time offset. Users choose wanted country in a slicer, to fetch the corresponding offset, measure could be similar to SelectedOffset=SELECTEDVALUE(MappingTable[Offset])

Best regards,

Yuliana Gu

[Mike282]

Any help on how to set the local time dynamically without having to hardcode a timezone offset in DAX?