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

Hi - thanks for the response and idea.  I can give that a try, but can you please confirm what you mean by "complete dates"?  I've tried with short and long dates that are complete, but get the same results - i.e., Wednesday, December 15, 2015  ->  12/15/2015, etc. 

ALLUREAN's avatar
ALLUREAN
Solution Sage
4 years ago

Can you try this:

TimeInService = SUMX('Equipment', DATEDIFF('Equipment'[DateFirstUse].[Day], 'Equipment'[DecommisionDate].[Day], DAY))