cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stevek Helper II
Helper II

power bi changes date from source data

When I get data from SharePoint, or refresh the data, Power BI changes the dates that are downloaded from SharePoint.

 

For instance I have a column called "Period Start" which is always the first day of the month, and a column called "Period End", which is always the last day of the month. Power BI changes the Period Start from the first day of the month to the last day of the previous month (eg from 1 Oct 2018 to 30 Sept 2018), and changes the Period End from the last day of the month to the second last day of the month (eg from 31 Oct 2018 to 30 Oct 2018).

 

It does this for other date columns as well. The date shown in the table is the day before the date in the original data source.

 

Why does it do this? And can I prevent it from happening?

 

Steve

5 REPLIES 5
v-piga-msft Resident Rockstar
Resident Rockstar

Re: power bi changes date from source data

Hi @stevek,

 

I'm afraid your issue may caused by the timezone.

 

You could have a reference of this similar thread and try the workaround which meet your scenario.

 

If you still need help, I need more details about your scenario. Which connector do you use in Power BI when get data from SharePoint? 

 

If it is convenient, please share some data sample which could reproduce your scenario so that I can have a test in my site.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
stevek Helper II
Helper II

Re: power bi changes date from source data

There appears to be an issue with the time zone and daylight saving and SharePoint. When I use an excel file for the source data, the dates are correct, but not from SharePoint.

 

I have set up SharePoint with the correct regional settings, but Power BI amends the dates when importing them.

 

I have included some sample data to show the issue. It can be found here:  https://www.dropbox.com/s/y75217g9al1mkoa/Example44.pbix?dl=0

 

For information the correct dates are shown in this table:

 

TitleInitiative NameStart DateOriginal End DateApproved End DateActual End Date
C0005101Project 129/01/201830/04/201831/07/20185/09/2018
C0008007Project 215/02/201820/12/201820/12/2018 
C0012001Project 314/05/201831/07/201831/07/2018 
C0021021Project 41/03/201831/12/201831/12/2018 
C0021022Project 51/05/201828/02/201928/02/2019 
C0021023Project 61/06/201828/02/201928/02/2019 
     

 

Thanks

Steve

v-piga-msft Resident Rockstar
Resident Rockstar

Re: power bi changes date from source data

Hi @stevek,

 

By my tests with your data sample, I cannot reproduce your scenario.

 

I created the SharePoint List with your data sample and get data with SharePoint list connector in Power BI, everything works as expected.

test result.PNG

 

 

Please check the timezone for your SharePoint and your Power BI.

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
stevek Helper II
Helper II

Re: power bi changes date from source data

Thanks Cherry

 

I have checked both SharePoint and Power BI settings and they are correct. So I am a bit stuck, without resort to a power query solution. 

 

Cheers

Steve

gpiero Resolver III
Resolver III

Re: power bi changes date from source data

@stevek

 

at the beginnig of my experience with Power BI I had the same issues with a Sharepoint custom list.

 

Please try to check if the post below could helpyou

Regards

https://community.powerbi.com/t5/Desktop/Wrong-input-in-a-field-Date/m-p/215986#M95632

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors