Time zone issue with Salesforce

[grodinsm]

I didn't notice the "don't post new issues here" on the old page until I was done posting my two issues, sorry for the duplication:

Salesforce reports time in UTC format with the correct time zone, but PowerBI seems to be misinterpreting the UTC time as the already offset time (it's not applying the UTC offset).

For example, 16:00:00 UTC -5 is seen inside of power BI. In Salesforce this same record shows 11:00AM EST. PowerBI should either be showing 16:00:00 UTC 0, or 11:00:00 UTC -5.

When you try to offset the time with the date/time/timezone functions, you get something like 11:00:00 UTC-10 which then gets adjusted to your local time zone automatically and you're again presented with 16:00:00!!!

[Anonymous]

Has anyone identified a fix?  From earlier comments it appears that the issue is the following:

"it seems their import tool only pulls in UTC time from Salesforce and also their servers are set to run on UTC time, and the code isn't built to accomodate for the user's UTC time settings, so even if you changed time zones on salesforce or on your computer, it wouldn't matter, you're stuck with PowerBi's time zone setting which i think they said was UTC 0."@grodinsm

[grodinsm]

@Anonymous not that I know of. The workaround i mentioned where you split the date and reformat it, etc... only works in desktop mode. web mode shows a blank column. From what I understood from the person at Microsoft that i spoke with, was that it was a massive undertaking to change how this all worked and it was low on the priority list. I've continued to just use the desktop version and my workaround for critical functions, and accept the incorrect data on my web reports. 

[des_san]

Is there an update to this Issue? I am seeing this issue today, May 2022, and trying to resolve it for my company.

Creating custom columns in Power BI desktop to extract the timezone & calculate a new value isn't a practical solution for my business case. We have way too many date fields in our salesforce datasets.

A problem for us is that some of our salesforce date fields have different timezones while others don't appear to have the timezone to be extracted.

Ex/ Created Date: 5/2/2022 9:00 -8:00

Ex/ Submitted Date: 5/2/2022 11:00 -7:00

Ex/ Received Date: 5/2/2022    (date only field)

This issue impacts team day to day reporting.

[grodinsm]

Nope!

The best I can suggest is to use a third party tool to import your salesforce data into an SQL database (we use Skyvia) and then run your reports from there. I can't check right now but I don't believe the time error happens in that situation. And even if it does, its way easier to do a stored procedure to adjust the times across a ton of fields in SQL than it is to try to edit it in the slow and gruelling editor that is in PowerBI.

[yiddy]

I've solved this by offsetting the hours within power query.

Orig value: 1/1/2022 10:00PM -4:00

Value should be corrected to: 1/1/2022 6:00PM -4:00

Method:

1. Format column to datetimezone. (1/1/2022 10:00PM -4:00)
2. Extract UTC hours offset (-4)
3. Remove timezone from original value (1/1/2022 10:00PM)
4. Add back timezone, but now with UTC-0 value (1/1/2022 10:00PM +0:00)
5. Now switch the zone based on the extracted value from step 2 (1/1/2022 6:00PM -4:00)

All of this can be done within a single calculated column. see below:

DateTimeZone.SwitchZone(
    DateTime.AddZone(
        DateTimeZone.RemoveZone([Email_Timestamp__c]),0
        ),
    DateTimeZone.ZoneHours([Email_Timestamp__c])
)

[Mazzy]

@yiddy @I use this formula and it works well in PBI desktop. The dates revert back to the original dates in PBI Server. 

[Anonymous]

Thank you, @yiddy . Such a simple and elegant solution to the infuriating problem of reconciling timezone date/times with PBI SF imports. 

As an aspiring PBI power user, your screenshots and snippet example are much appreciated - I learned a lot today.