Forum Discussion
DateDiff unexpected results
Dear all,
I am calculating a new column with the difference in days of [Due Date] and [Posting Date]. I am using a simple DATEDIFF function:
This is giving me bad results in some cases:
and good results in most of the table:
This doesn't make any sense to me at all. Can anybody give me an idea of what could be wrong?
On a separate note, is it possible to show the DATEDIFF values on positive and negative values? If Posting Date is after the due date, I would like a positive value and not 0
Thanks in advance
Hi setis,
I tested with above DAX formula, it returns correct datediff results.
In your scenario, please check if the results are correct in Data view as shown in above screenshot. If you have several duplicate rows, when you add fields into table visual, [Diff PostingDate & DueDate] might be aggregated which returns larger values.
On a separate note, is it possible to show the DATEDIFF values on positive and negative values? If Posting Date is after the due date, I would like a positive value and not 0
You can use a IF condition in such a scenario. Similar to:
Diff PostingDate & DueDate = VAR datediffval = DATEDIFF ( 'Sample Table'[Due Date].[Date], 'Sample Table'[Posting Date].[Date], DAY ) RETURN IF ( 'Sample Table'[Posting Date].[Date] > 'Sample Table'[Due Date].[Date], ABS ( datediffval ), datediffval )Best regards,Yuliana Gu
3 Replies
Hi setis,
I tested with above DAX formula, it returns correct datediff results.
In your scenario, please check if the results are correct in Data view as shown in above screenshot. If you have several duplicate rows, when you add fields into table visual, [Diff PostingDate & DueDate] might be aggregated which returns larger values.
On a separate note, is it possible to show the DATEDIFF values on positive and negative values? If Posting Date is after the due date, I would like a positive value and not 0
You can use a IF condition in such a scenario. Similar to:
Diff PostingDate & DueDate = VAR datediffval = DATEDIFF ( 'Sample Table'[Due Date].[Date], 'Sample Table'[Posting Date].[Date], DAY ) RETURN IF ( 'Sample Table'[Posting Date].[Date] > 'Sample Table'[Due Date].[Date], ABS ( datediffval ), datediffval )Best regards,Yuliana GuDear Yuliana,
Thank you so much for your answer.
You were right. I have duplicate dates and the results are right in Data View.
