Forum Discussion
DATEDIFF Incorrect Conversion Output
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.
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.
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?
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.
I have messed around with the data types and formatting already, but still with no success. They are true DATE data types. I'll mark your answer as the solution and try to find a workaround. Thanks all for trying to help.
The other thing to check, if any grouping/aggregation happening on the visualisation is causing misleading results. If it is already dumped into a table with no aggregation happening then I'm out of ideas for now.
Best of luck.
Hi, KNP
It seems that the incorrect values are multiplied by 2,3,4 maybe other, did not checked all of them, but something is repeating it. Thanks for the solution.
I tested using a measure, based on your file and it is working also.
DateDiff_Measure = SUMX('Equipment', DATEDIFF('Equipment'[DateFirstUse], 'Equipment'[DecommissionDate], DAY))
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.
