Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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.
Proud to be a Super User!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
87 | |
74 | |
66 |
User | Count |
---|---|
124 | |
113 | |
96 | |
80 | |
72 |