Server Refresh Changing SharePoint Timestamp - Desktop Version Works Just Fine

[Anonymous]

I have a dashboard that I created with PowerBI Desktop, which I publish to workspace that many people in our organization have access to. The dashboard sources data from a SharePpoint List.

Where I am having the issue is with a simple measures I have to calculate the difference between two time stamps from SharePoint, utilizing the following DAX:

Time to Fulfill (Minutes) = DATEDIFF([ID Created],[Fulfilled Date], MINUTE)

The measure works just fine in desktop mode when I refresh, but once it gets published and the first automatic refresh occurs the measure becomes incorrect.

CorrectWrong

I downloaded the PBIX file from my published workspace and looked at the data and see that the time stamp is changed by 5 hours, but for only one of my data fields. In this case it is the "ID Created" field that has the issue.

CorrectWrong

Can someone explain why the data being extracted from SharePoint is having its time stamp changed by 5 hours when the server refresh is performed? Also, why does it only happen to this one field? Does it have anything to do with the fact that "ID Created" was the default SharePoint column and the header that has no issues, "Fulfilled Date" is a calculated column?

How can I fix this?

[Anonymous]

@GilbertQ 

I created a new column and added time to the orignal timestamp field using the following DAX:

ID Created Modified = [ID Created] + ((1/24)*5)

Again, in Desktop it looked correct, but when I published and did a server refresh it only compounded the problem again.

Here is the desktop dataset. It added the 5 hours as expected.

Original - CorrectModified Original - Correct

Here is the server refreshed version.

Server Version - WrongServer Version - Wrong

Any other ideas?

[GilbertQ]

Did this work for you after the dataset was refreshed?

[GilbertQ · Accepted solution]

Hi @Anonymous 

The reason I think that this happens is that when it gets the ID Created date it gets it from the local time of the PC/Server that it is on

When you are working on your PC it uses your local time. But when data is uploaded to the Power BI Service it is then on the Power BI Servers times which is UTC. 

What would would need to do is to add 5 hours to the ID created time so when it is in the Power BI Service it will calculate the date correctly.

[Anonymous]

@GilbertQ 

Ok! After some more investigation, I had to rollback some changes in Power Query that I had originally made trying to solve this. Once I did that I applied your suggestion everything worked!

Thank you!

[GilbertQ]

Hi @Anonymous 

That is awesome to hear thanks!