Forum Discussion

gpiero's avatar
gpiero
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

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.

 

 

 

 

Need Help

9 Replies

  • ankitpatira's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    10 years ago

    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 ?

    • gpiero's avatar
      gpiero
      Icon for Skilled Sharer rankSkilled Sharer
      10 years ago

      ankitpatira

       

      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

       

      • gpiero's avatar
        gpiero
        Icon for Skilled Sharer rankSkilled Sharer
        10 years ago

        ankitpatira

         

        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.