Forum Discussion

sffc's avatar
sffc
Helper I
4 years ago
Solved

DATEDIFF Incorrect Conversion Output

The DATEDIFF calucation is outputting incorrect values as can be seen in the screen capture below.  Is there a specific reason why that anyone is aware of, or is there a more accurate method to get t...
Need Help
  • KNP's avatar
    KNP
    4 years ago

    I've only got working results also.

    See updated PBIX attached. Page 2.

    The first place I'd be looking is the data types for the dates and see if there are any conversion issues/locale issues, but I doubt that is going to account for such significant differences.

     

  • sffc's avatar
    sffc
    4 years ago

    Add custom column:

    = Duration.Days([DecommissionDate]-[DateFirstUse]))
    Power BI doesn't like calculated date columns with a SQL datasource.  Adding a custom column in Power Query produces correct calculations for all values.

sffc's avatar
sffc
Helper I
4 years ago

This is in a calculated column.  I don't see a way to use columns in measures, so I've been using calculated columns:

timeInService = DATEDIFF(Equipment[DateFirstUse], Equipment[DecommissionDate], DAY)
 
I keep getting an HTML error upon posting the reply when trying to paste the table in the response, and I don't see an option to necessarily import or attach anything.  What is the best way to supply the sample data in this form?

My apologies, but I cannot share a screen shot of the model.  

 

The DATEDIF() function in Excel calculates the values correctly, so this appears to be an issue specific to Power BI for some reason.  In Power BI, some of the values are correctly coverted, but a majority of them are incorrect.

KNP's avatar
KNP
Super User
4 years ago

You should just be able to copy and paste from excel.

Alternately, share in google drive or similar.

 

This was copy/pasted from excel...

DateStart DateEnd
18/05/2019 4/12/2011
2/09/2011 1/07/2019
11/10/2010 11/08/2013
28/09/2010 20/01/2017
4/09/2011 19/09/2018
17/07/2013 7/12/2009
7/08/2013 10/02/2017
5/06/2014 14/03/2010
15/02/2019 10/08/2021
21/05/2013 4/06/2010
5/07/2019 20/01/2012
17/12/2013 17/07/2021
15/11/2012 14/05/2018
16/02/2017 3/01/2014

 

  • sffc's avatar
    sffc
    Helper I
    4 years ago

    Yeah, I only get this error when attempting to post a reply with the pasted table -

    "Your post has been changed because invalid HTML was found in the message body. The invalid HTML has been removed. Please review the message and submit the message when you are satisfied."

     

    Here is a Google Drive link I have uploaded a sample to -

    https://drive.google.com/file/d/1t7jBXYHQ367BWpeuj-x9Y0KPmlsQrpMX/view?usp=sharing

    • KNP's avatar
      KNP
      Super User
      4 years ago

      While I'm looking at your sample data, attached is a basic PBIX example where datediff appears to be working as expected. Just in case that helps you to identify the issue.

       

      • sffc's avatar
        sffc
        Helper I
        4 years ago

        Yeah, I cannot replicate the issue when using a spreadsheet as the datasource.  All of the values when Excel is the datasource come out to be correctly converted.  When using values with Azure SQL database as the datasource, it's a mix of correct and incorrect values.  I would assume the result should be the same, so I am not sure why using the database table would produce different results.  Any thoughts?