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.

KNP's avatar
KNP
Super User
4 years ago

DateDiff should work fine.

Need more detail.

Is that a measure or calculated column?

Can you include some actual data? (not a screen shot)

A screen shot of your data model may also help.

 

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.