cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HenryH
Regular Visitor

SharePoint date differs from Power BI date

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

39 REPLIES 39
Lorence
Frequent Visitor

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

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.

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

I can confirm this worked for me!

nickdewitt
Frequent Visitor

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.

unrly
Frequent Visitor

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

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
Not applicable

Hi

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

pds
New Member

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
Not applicable

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
Not applicable

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
Not applicable

Last bump.

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

dannn777
Frequent Visitor

this fixes the error

1.PNG

 

2.PNG

 

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
Not applicable

did you found a solution for this problem?

Anonymous
Not applicable

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

 

Ricsta
Frequent Visitor

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

Anonymous
Not applicable

@Ricsta , @alltasksIT Did you ever find a solid solution when publishing your reports? I have the same issue, dates look fine in the Desktop after changing the locale to UK. But when I publish to the server, which is placed in UTC+2, the issue reappears.

 

My SharePoint site is Non-English and therefore has to be API-version 15. So changing this is not a solution. 

We've had the bizarre situation where we have had a mix of correct dates and dates out by one hour within the same column on a List on SharePoint Online. Fortunately we also had the dates elsewhere so I could check them. I used this formula to correct them:

 

each if DateTime.Time([Lease date]) = #time(0,0,0) then [Lease date] else Date.AddDays ([Lease date], 1)

 

So if the Lease date is correct (ie with a time of 00:00:00), then it uses it otherwise it adds a day.

 

I've seen this behaviour on two seperate SharePoint lists in the last few weeks.

AleksanderPunt
Helper I
Helper I

Having the same issue!

When are they fixing this?? Or is it because of incorrect regional time settings within Power BI/Sharepoint?

 

We add items in the sharepoint list with current dates (probably at midnight), but an hour gets subtracted when importing the dataset in Power BI. So "Dec 1st - 00:00" becomes "Nov 30th - 23:00".

 

Irritating when doing monthly analysis.. A workaround is to create calculated columns in the sharepoint list with year, month and day - BUT it can't be used as a timestamp-filter within Power BI.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors