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

[v-haibl-msft]

@grodinsm

I cannot repro the same issue as you. I get data from Salesforce in Nov 2016 version of Power BI Desktop. There is a Sample Report: Login Leaderboard in Salesforce. The Login Date/Time column data are same in Power BI Desktop and Salesforce as below. The time zone seems to be UTC -8.

Did you see the issue in different specific reports?

 

Best Regards,

Herbert

[grodinsm]

Where do I go to find that sample data? What time does it show IN salesforce? I'm willing to bet Salesforce does not show that first one as 9:35PM.

Change that column in your query to Date/Time/Timezone and it'll say 9:35PM UTC -08:00

If you logged into Salesforce to look at that same data or got an excel download you'd see the times do not line up. it would say in the settings that the time zone is UTC -08:00 and that that fist "login" record happened at 1:35PM. 

PowerBI is importing the UTC time and time zone, but not applying the time zone or importing the adjusted time. 1:35PM should be displayed as:

1:35PM or

1:35PM UTC -08:00 or

9:35PM UTC 00:00

[v-haibl-msft]

@grodinsm

I create a custom report in salesforce which contains several date/time columns as below.

When I import the data into Power BI Desktop, the date/time values are actually same as the ones in salesforce as below.

The only problem is that the data type is Text in Query Editor. But the date and time values are still same.

Best Regards,

Herbert

[grodinsm]

@v-haibl-msft

Interesting, I was pulling directly from their data tables, not importing a report. I will try that out and see if it helps. The other thing I notice is you're running Salesforce Lightning and I'm using Salesforce Classic. 

[grodinsm]

Hello @v-haibl-msft, 

I had to put this off for a while, was working on a more important project. I've done as you said above and you are correct, when you use Salesforce Reports the time is imported correctly, the same fields pulled in via Salesforce Objects shows the incorrect timezone. Unfortunately, PowerBI is limited to 2000 rows through salesforce, and I need to pull in 14,000+ rows, so I am stuck with Salesforce Objects.

Help!

[emilesteenkamp]

W're currently facing the same issue, the user who is logged into SFDC has a default timezone set to UTC+12, the default org timezone is UTC+1, we calculated the date in PBI read directly from the object to be UTC-1 so not sure how this is calculated at all...

[grodinsm]

@emilesteenkamp I had a lengthy back and forth with Microsoft on this one, 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. it's on their list of things to fix, but it would require a very big re-write of powerBI so it's almost at the bottom of the list. don't hold out hope. 

[rjbnorth]

So can anyone recommend a workaround until this is resolved?

[grodinsm]

@rjbnorth  My workaround was a very complicated mess of taking the column, split the time zone information out into another column that was a formatted number.. then I think another column that added the correct offset to that number, then another column that added the timezone back into the date as a string, and then ANOTHER column that took that string and converted it back to date/time/timezone. Something like that. I'm afraid to even look at the table editor to see what I did in fears of breaking it. I use this weekly to report on data. 

It took a lot of trial and error but now it works perectly..... in PBI Desktop. Online the column appears blank.