DateDiff unexpected results

[setis]

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

[v-yulgu-msft · Accepted 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
    )

Best regards,

Yuliana Gu

[setis]

Dear Yuliana,

Thank you so much for your answer.

You were right. I have duplicate dates and the results are right in Data View.

[AlB]

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