Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Time zone issue with Salesforce

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!!!

Status: Needs Info
Comments
feedbackisagift
Frequent Visitor

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
Frequent Visitor

@feedbackisagift 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
Helper I

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
Frequent Visitor

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
Regular Visitor

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

2022-07-31 22_37_22-Diezx_Rack price_Data file - Power Query Editor.png2022-07-31 22_37_03-Diezx_Rack price_Data file - Power Query Editor.png

Mazzy
New Member

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

timv
New Member

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.