Forum Discussion

svishwanathan's avatar
svishwanathan
Helper III
8 years ago
Solved

Days between dates column not correctly computing

Hello I am running two columns which cacluate days between dates and both are throwing incorrect results   Time to Close RFIs = IF(ISBLANK(RFIs[ANSWERED_DATE]),BLANK(),(RFIs[ANSWERED_DATE]-RFIs[...
Need Help
  • Yggdrasill's avatar
    Yggdrasill
    8 years ago

    Recreated your table and created two different calculated columns with the same results

     

    datediff =
IF (
    DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) > 0,
    -1 * ( DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) ),
    DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY )
)

    where

    • 'Table2' should be replaced with your table and
    • RequiredDate is PMSCRFI_ANSWERED_DATE and
    • AnswerDate is PMSCRFI_REQUIRED_DATE

    Another Calculated column

    datediff 2 =
IF (
    Table2[RequiredDate] < Table2[AnswerDate],
    -1 * DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ),
    DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY )
)

    These give the same result

     

Anonymous's avatar
Anonymous
Not applicable
8 years ago

The formula does look like it produces the correct answer sometimes, but not all the time.

 

In your table, are you summarizing your Date fields in any way?  For example are you showing "First" or "Last" date for any reason?  I'm wondering if your data is in such a way that your formula is calculating using a different context to how your matrix is displaying the rows.