Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 )
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 )
Dear Yuliana,
Thank you so much for your answer.
You were right. I have duplicate dates and the results are right in Data View.
Hi @setis
Maybe if you share your pbix someone will be able to help. It is hard like this, as you can see by the number of responses so far
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
82 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |