Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sffc
Helper I
Helper I

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 the count of days between Date A - Date B?

 

sffc_0-1642624374275.png

 

2 ACCEPTED SOLUTIONS

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

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.

View solution in original post

17 REPLIES 17
KNP
Super User
Super User

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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.

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

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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))




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




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.

 

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

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
Helper I
Helper I

The results for the above function can be seen in the screen capture below that are subtsantially higher -


sffc_0-1642627270212.png

 

Not sure that will help, but the last .[Date] should be .[Day]. It will be good if you provide some file if not contain any sensitive info




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




I must've changed that having to retype some of the table names, but I changed it to .[Day] and still get incorrect results.

ALLUREAN
Solution Sage
Solution Sage

Hi, @sffc 

Try creating a calendar table with complete dates, then connect it to the fact table (Equipment) and use the function. 

Sample calendar table:

Date = ADDCOLUMNS(CALENDAR(
DATE(YEAR(MIN(%YourTable[YourColumn]%)), 1, 1),
DATE(YEAR(MAX(%YourTable[YourColumn]%)), 12, 1)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"MonthNo", FORMAT ( [Date], "MM" ),
"YearMonthNo", FORMAT ( [Date], "YYYY/MM" ),
"YearMonth", FORMAT ( [Date], "YYYY/mmm" ),
"MonthShort", FORMAT ( [Date], "mmm" ),
"MonthLong", FORMAT ( [Date], "mmmm" ),
"WeekNo", WEEKDAY ( [Date] ),
"WeekDay", FORMAT ( [Date], "dddd" ),
"WeekDayShort", FORMAT ( [Date], "dddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date],"Q" )
)



Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




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. 

Can you try this:

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




Did I answer your question? Mark my post as a solution!


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.