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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Skilled Sharer
Skilled Sharer

Wrong input in a field Date

I'm facing a very courios issue.


I have a list in Sharepoint where my Supplier write a Date.



The list is corretly formatted in Sharepoint.


When upload the data in Power BI I am getting this



Since the date it is not correct, whatever statistics wil be wrong.


I tought I have found the solution as showed below

DDT IssueDateNew = DATEVALUE(day('Third Party Delivery Notes'[DDT issue date]) + 1 &"/"& month('Third Party Delivery Notes'[DDT issue date]) & "/"& YEAR('Third Party Delivery Notes'[DDT issue date])) & " "& TIMEVALUE(hour('Third Party Delivery Notes'[DDT issue date]) - 22 &":"& MINUTE('Third Party Delivery Notes'[DDT issue date]) &":"& SECOND('Third Party Delivery Notes'[DDT issue date]))

But it does not work when the date to evaluate is 31/03/2016: Power BI calculate 32/03/2016!!.


So my question is: which is the best practise to solve this issue from the root? Why a field date in Sharepoint is managed in this way by PBI?


Thanks in advance for any suggestion.





If I can...
Community Champion
Community Champion

@gpiero Fact that powerbi calculates '32/03/2016' means that it is not identifying column as a date column. Have you checked that imported column is date type column ? From DAX you provided you're extract day out of date field and adding 1 to it. I see you've used outer DATEVALUE function but since it has returned back 32 means DATEVALUE function is probably not working. May I ask why you need to use that DAX to be able to format date ?



I fear I have not explained clearly the basic issue. Let me try once again step by step.


My Supplier put the right date: 10/05/2016.

PBI write 09/05/2016 22:00:00 and here the issue arise.


Due to this PBI behavior the perfomance of my Supplier is not right


Here you are how the data has been imported in PBI

= Table.TransformColumnTypes(#"Rimosse colonne",{{"Id", Int64.Type}, {"DDT issue date", type datetime}, {"DDT approved date", type datetime}, {"FornitoriId", Int64.Type}})

 Anyway I'll check again importing the same data in a new table


If I can...



I have repeated the import


= Table.TransformColumnTypes(#"Ordinate righe",{{"DDT issue date", type date}})




Before applying the query, in the column DDT issue date you could find 12/05/2016 22:00:00. Now it is type date but should be 13/05/2016, because this is the data put in the Sharepoint.


Do you have some other suggestion? I did not trasformed yet the column DDT approved date in case we want try something else.


If I can...


thanks to your observation I realized that one of the two columns was wrongly defined.

In fact now I do not need to put DATEVALUE.



Anyway the basic issue of the datetime that come from Sharepoint list remain and I solved it ( temporarly I hope)  as shown below.


DDT IssueDateNew = day(('Third Party Delivery Notes'[DDT issue date]) + 1) &"/"& month('Third Party Delivery Notes'[DDT issue date]) & "/"& YEAR('Third Party Delivery Notes'[DDT issue date]) & " "& hour('Third Party Delivery Notes'[DDT issue date]) - 22 &":"& MINUTE('Third Party Delivery Notes'[DDT issue date]) &":"& SECOND('Third Party Delivery Notes'[DDT issue date])


I wonder if someone else has faced the same issue ad how it has been solved.


Thanks again

If I can...

What is the DateTime format supported by Power BI to push data in that column?


Thank for your message.


The problem arose last May had two variable: the first one my poor knowledge at the beginning, the second one a behaviour the MS support has defined "by design of Sharepoint".


Now I solved temporary the problem using a workaround suggested by MS tech support.


It consist to add some code in the query that trasform properly the field Date manually inserted in the Sharepoint list.



If I can...
Not applicable

Hi @gpiero,


Can you share your solution? I'm facing the same issue importing dates from Sharepoint lists. Both are DateTime columns, but I've 1 day difference like you.

Hi @Anonymous

during the last months I tried several solutions, included the workaround proposed by MS Support.



I would like to share the last solution I found. I think it is the best one because you do not need to add a calculated column to your data.


I changed data connection on all my PBI report using the option ODATA feed from Get Data.


Source = SharePoint.Tables("", [ApiVersion = 15]),
    #"9a424f75-b565-498f-a57f-220da7d75fdf" = Source{[Id="9a424f75-b565-498f-a57f-220da7d75fdf"]}[Items],


Here you will find the right value for your Datetime column.



Here the string to connect to the site the contains your Sharepoint list.




If I can...
Not applicable



OData.Feed solution works like a charm, thanks for the tip. Smiley Happy

Helpful resources


Fabric certifications survey

Certification feedback opportunity for the community.


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.