Date 1 day behind source date

[Cul33]

I am importing various date fields from a SharePoint site - however, every date shows the previous day. 

The SharePoint time zone is set for UTC Dublin, Edinburgh, Lisbon London and the locale English UK; Power BI locale for import is set to English UK. 

For this particular data, time is not relevant in the source SharePoint column, hence fields  are set as date only, rather than date & time. 

A SharePoint date of 22/09/2020 shows in Power BI as 21/09/2020. If change type to date/time is selected, the time is 00:00:00.  I could use a workaround and set the field to date/time in SharePoint with default of 12PM instead of 12AM, but this messes up the display format of the list.

In other scenarios where I have used fields where time is relevant, I have changed the type to Date/Time/Timezone then created a custom column based on that column in the query editor =DateTimeZone.ToLocal([datefieldname]) - which works.

In this scenario, is the only solution to this to add a custom column to manually add some hours to the date for every single date field that I have (either in the M script or a custom column)? Or is there a better option? 

Any advice greatly appreciated.

[Gimmerzz]

@Cul33 I found your post as I had the same issue. The only way I found a way to get the dates to match from sharepoint to powerbi came down to something so simple I can't believe it. When you get data, choose 'More' then 'Online Services' then 'Sharepoint Online List' (I know you could select Sharepoint list in a quicker way than this), add your sharepoint list url and CHANGE implementation from 1.0 to 2.0 THIS is the vital step I found to solve my woes! Hope it helps anyone else, I'll sleep easy knowing this is working...at least for me!

[mira98]

this works also for me.thanks

[ashar1990]

this works perfectly, thank you !!

[slinfoo4]

This still seems to be an issue with Power Query. I exported a Microsoft Access Table to a Sharepoint List, then accessed using power query in Excel to create some reports. 

The Dates are still a day behind. 

SharePoint is set to UTC - Dublin Edingbrough Lisbon London and Power Query is set to English United Kingdom.

It's a shame we still have to manually change column values to fix this.

Edit - After some more digging the values are only an hour behind. Due to the original date/times being midnight they result in being 11pm the previous day. This means I only had to add an hour on to correct the dates.

[Greg_Deckler · Accepted solution]

@Cul33 - Smells like a timezone problem but hard to say. I suppose you either need to track down the discrepency in the date/time setting. Dublin is +1 UTC so that's almost assuredly where your issue is coming into play. The time is some date at 12 AM, you subtract an hour and it is the previous day.

So, your options are probably to track down the source of who is assuming UTC (probably Power Query/Power BI) and make it see reason, or you could fix it with a calculated column. In Power Query that would be:

Date.AddDays([Column1],1)

In DAX:

[Column1]+1

[Cul33]

Many thanks for your feedback @Greg_Deckler.

Although unclear why if the time zones match in SharePoint and Power BI, I can use this as a workaround - although it occurred to me that if in the future it is "fixed", my dates could end up being 1 day ahead!

As well as Greg's method, I tried the method described in the Curbal video, where Order Date is the date field, and having set the type to be date time, =DateTime.From(["Order Date"])+#duration(1,0,0,0).  This also worked. 

I have alot of date fields, so will copy & paste them in the query editor then change the field names to speed it up.

[amitchandak]

@Cul33 , refer if these can help

https://www.poweredsolutions.co/2019/10/21/handling-different-time-zones-in-power-bi-power-query/
https://community.powerbi.com/t5/Desktop/Convert-UTC-to-client-time-zones/td-p/56337
https://www.youtube.com/watch?v=M1zquwmpnZE