SharePoint date differs from Power BI date

[HenryH]

Hi,

I have imported data from a SharePoint list into Power BI including one column containing a date.

The date in Power BI differs 1 day from the date in SharePoint.

e.g. Sharepoint: 1/6/2016 > Power BI: 1/5/2016

Is this related to time zone differences and how can I fix this except for adding a new column adding one day?

Kind regards,

Henry

[Lorence]

This seems to work fine! not too difficult but requires work on each date column.

http://geekswithblogs.net/darrengosbell/archive/2019/02/01/power-bi-ndash-fixing-dates-from-sharepoi...

[krishnakantht7]

Hi lorence, website link is not able to access to go through the steps and solve date issue differing in sharepoint & powerbi

[Anonymous]

These solutions work only if you are not republishing back to a server/

For me, ive tried all of the above, it works for a little while then the server weirdly reverts back to the date -1.

The only solution ive now gone with is reverting back to api 14. Im hoping it works as ive reepublished to the server. So far so good.

[Atty]

Changing the API version from 15 to 14 worked for me. Deleted all steps except source after changing and redid what I had done.

Question is, what ramifications does changing the api version have?

[jstorm]

Has anyone confirmed their locale is set correctly in Sharepoint for the entire site?

https://support.microsoft.com/en-gb/office/change-regional-settings-for-a-site-e9e189c7-16e3-45d3-a0...

And also in Power BI Service? (NOT power BI Desktop)

https://docs.microsoft.com/en-us/power-bi/supported-languages-countries-regions

[Anonymous]

I can confirm this worked for me!

[nickdewitt]

Yeah this is pretty bad, made worse by the fact that this has worked in the past the same on the service as it did on the desktop. Someone has seriously dropped the ball here, we don't seem to have control over what locale the service performs it's refresh in and even when you specify the locale as in the previous post, it still does not work the same on the service as it does on the desktop.

I'm in the uk, so eventually I just gave up, converted it to a float, rounded it then converted back to a date. That should work for anyone who is less than 12 hours away from UTC.

Stupid problem, should not be resurfacing on reports that I wrote years ago and applied the datetimezone conversion to appropriately.

[Anonymous]

Just came across this thread when having the same issues. I seem to have sort of worked it out, I have a Date/Time column in SP that comes in as text to Desktop and it is jumping ahead 6 hours. Note this is only for local time I believe.

1. Open Power Query
2. Change Data Type to Date/Time/Timezone
3. Change Date & Time Column, Time to Local Time
4. Change Data Type to Date/Time to remove the Timezone

No idea why this is doing it, but it gets me the correct time now. Now, if only there were more visuals that worked with time...

[Ricsta]

I have the same issue, and have had it for months now. I too thought I had found the solution in the 4 steps pointed out by unrly above, but as stated this is only for local time. As soon as I publish, the problem reappears. Eventually I had to go for the solution of adding a couple of hours. However, now I am in a position where I am querying a list that is integrated with a third application (MS Project) which by default sets the time in the DateTime to actual times and not to 00:00 like SharePoint-lists do when opting to not include time in DateTime-columns of lists. 

The result of this: 

When updating the date in MSP, the date is set correctly (think it still deducts an hour, but with all Time-variables being set between 07:00-17:00 by default depending on whether it is Start- or Finish-date this is not a big problem). However, if I update the same row in the list-version the Time-variables uses SharePoint-defaults (00:00) instead of MSP-defaults. In other words, if I update in MSP I get one result, but if I update in the list-function I get another. This is problematic as it is intended for different users, some preferring to work in MSP and syncronizing to the SharePoint-list and others preferring to work directly in the SharePoint-list itself. 

I realize that I can use the same solution as long as the Time-variables are not altered (as 07:00 becomes 05:00 and is still within the same Date), but it is surely not a flawless option. Another application might have another default Time-variable, some users might have the need to set the Time-variable to 23:00 or 01:00, etc. 

A proper solution to this problem would surely be welcomed. 

[Anonymous]

Hi

I just followed your steps unrly and it does seem to work, thanks.

[pds]

This issue still persists.  None of the solutions work.

As a work around creating a calculated column in the SharePoint:

=DAY([Column with date])&"/"&MONTH([Column with date])&"/"&YEAR([Column with date])

Then I changed the format of the column in PowerBI to Date and worked on from there.

This is a real pain as it throws out month end calculations.

[Anonymous]

Has there been any found solution to this? With the clocks going forward last Sunday, data I am pulling through is now on the incorrect day (instead of 01/04/2019, they read 31/03/2019 23:00:00). Changing the Locale etc doesnt seem to work. Note: This is with a simple: Query->From SharePoint List ->Link to list.

If I change the APIVersion from 15 to 14, it works, but this seems like a bad idea as I saw a post commenting that this causes issues with publishing.

Is there any way to completely fix this? It is clearly daylight saving messing things up

[Anonymous]

Bumping this.

I have seen this issue posted many times with no clear resolution for it. At the moment, all I can think of is to add an hour to the dates to bring them to the correct date, but that doesn't seem reliable. Changing the Locale doesn't make a difference either. Only changing the API Version to 14 does.

Any help?

[Anonymous]

Last bump.

I have just resorted to adding an hour to the date before I convert from datetime to date.

[dannn777]

this fixes the error

[CoRouff]

Power BI Desktop

Version : 2.61.5192.601 64-bit (août 2018)

I have the same issue and the lastest suggestion did not fix it.

Can anyone from the Power BI team react on this issue ?

Its quite annoying and a real struggle to get it working as intended without massive workarounds.

[Anonymous]

did you found a solution for this problem?

[Anonymous]

In Power Query, I enter the following, for a column called Date.

#"Replaced Value" = Table.ReplaceValue(<Source>,each [Date],each [Date]+#duration(0,1,0,0),Replacer.ReplaceValue,{"Date"}),

For UK this will bring a 22/04 23:00 to 23/04 00:00, which I convert to Date 23/04. Outside Daylight saving, it would turn 23/04 00:00 to 23/04 01:00, which again would convert to 23/04. So for me this is enough.

There is also the suggestion which works of casting to date, then date time locale, then back to date (three separate steps).

[Anonymous]

Thank you!

[Ricsta]

I tried the date time locale alternative, which almost fooled me to think it was working, but as soon as I published the report online it reverted (I reckoned it used another source for the date time locale setting after being published).