Date value difference between SharePoint data source and power BI

[Arun_BI]

Hi All,

I have a SharePoint list and connected that to Power BI. I see a 1-day difference between Power BI and the SharePoint list for a date column. Attached is the sample image for reference.

'For eg , In my SharePoint list, the date value is 26-May-22 and in power BI it is showing as 25-May-22

Sharepoint List: 

Power BI Data: 

Please let me know why there is an issue with a difference of 1 day. 

Thanks

Arun

[sompolk]

I recommend you create new column DateAdj by adj hr value such as DateAdj=[Date]+#duration(0,7,0,0) afterthat publish to workspace and refresh again. Sometime time on PWBI Desktop and Workspace isn't the same value.

[danextian]

Hi @Arun_BI ,

Possibly because they are using different timezones. If im not mistaken Power BI UTC. Try checking the timezone your SharePoint site is set to.

Site Settings >> Click on “Regional Settings” under “Site Administration”
On the Regional settings page, You can set the time zone, locale, and other regional settings.

[Arun_BI]

Hi,

My SharePoint site settings seem to be fine. I don't have any issues with SharePoint data since it is showing correctly as 26-May-22 but in power BI it is showing as 25-May-22 which is wrong. Do you know how to change the timezone in Power BI 

[AlexanderPrime · Accepted solution]

Power BI only handles using UTC for data but SharePoint is possibly reporting the data in the +1 timezone.

SharePoint will record dates in it's database as a Date/Time by default, but for Midnight local time for the current date as per your SharePoint site's locale settings, it will convert that into a display friendly date only format on your sharepoint list, so daylight savings will cause havoc with this if you are trying to export this list data for use in anything else.

In Power Query, if you change the column to Date/Time. Does it show the previous day but 11pm or earlier? A bit of a awkward workaround is to add a Power Query step to set up a custom column to add a few hours to the date column, usually for the difference you see but may be worth adding 1 more hour as if you have daylight savings times in your locale, then in October this will happen again, but then format it back to Date Only in Power Query so you don't notice a difference.

[Fart_Sniffer]

Situationally it may be easier to put appropriate time info into Sharepoint if using something like PowerApps as a front end.  Ensure that your Sharepoint date column is set to Date AND TIME, and ensure the time is entered an hour or two past the default, which is midnight. When daylight saving kicks in and pulls it back by an hour, it will no longer be 23:00 the previous day. If you are using a date picker in PowerApps and Sharepoint as your back end, you can either set the hour to default to 2am or 3am in PA.. or you can change coding in the update field of the datacard to something like "DataCardValue34.SelectedDate + Time(Value("3"), Value("0"), 0)". Ensure your regional settings for everything is set to local. I appreciate this is unlikely to help the original poster, but may be helpful to anyone searching the Internet for solutions.