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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Cul33
Frequent Visitor

Date 1 day behind source date

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.

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

7 REPLIES 7
Gimmerzz
Regular Visitor

@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!

this works also for me.thanks

this works perfectly, thank you !!

slinfoo4
Frequent Visitor

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
Super User
Super User

@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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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