Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
PBILearner2022
Frequent Visitor

Date value difference between SharePoint and Powerbi

Hi all,

 

I've pulling data from a Sharepoint list, but the date values in PowerBi are off by 1 day. For example, I have a datecompleted of 01/02/2024 in my Sharepoint list, but in PowerBi it shows as 31/01/2024. 

I tried to change API version for the sharepoint source in Advanced editor from 15 to 14. I am getting error .

PBILearner2022_0-1708535384136.png

 

I am struck with this issue can anyone help me on fixing it 

1 ACCEPTED SOLUTION

Hi @PBILearner2022 

 

Assume that you are using UTC+8 locale datetimes, add a custom column with below code (change 8 according to your locale). 

 

DateTimeZone.SwitchZone(DateTime.AddZone([Date], 0), 8)

vjingzhanmsft_0-1708659307784.png

Then change this column to DateTime or Date data type per your need. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

View solution in original post

4 REPLIES 4
PBILearner2022
Frequent Visitor

Thank you @v-jingzhan-msft  I will try it out today and let you know 

v-jingzhan-msft
Community Support
Community Support

Hi @PBILearner2022 

 

This is caused by the timezone difference as Power BI uses the UTC datetimes when it gets data from SharePoint while the SharePoint displays the datetime in your locale. To resolve this, you can use Power Query Editor to create a custom column to convert the UTC datetimes into your locale's datetimes. Then use the new column for further reporting. 

 

Here are some similar threads for your reference:

Date value difference between SharePoint data sour... - Microsoft Fabric Community

timezone - SharePoint Date wrong in Power Bi - SharePoint Stack Exchange

 

Here is a detailed blog introducing several workarounds for this:

How-To: Convert UTC to Your Local Time Zone in Power Query

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

I tried to create a duplicate column and changed it to local time  and it didnt worked 

Eg: It should show : 02/01/2024 instead it shows 01/31/2024

 

PBILearner2022_2-1708594871990.png

Could you please help 

 

 

Hi @PBILearner2022 

 

Assume that you are using UTC+8 locale datetimes, add a custom column with below code (change 8 according to your locale). 

 

DateTimeZone.SwitchZone(DateTime.AddZone([Date], 0), 8)

vjingzhanmsft_0-1708659307784.png

Then change this column to DateTime or Date data type per your need. 

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.