Forum Discussion

svishwanathan's avatar
svishwanathan
Icon for Helper III rankHelper 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

     

Yggdrasill's avatar
Yggdrasill
Icon for Responsive Resident rankResponsive Resident
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

 

  • svishwanathan's avatar
    svishwanathan
    Icon for Helper III rankHelper III
    8 years ago

    Thanks everyone for stepping in

     

    Both your answers were correct

     

    Yes my filter context in the table was incorrect which was giving inconsistent results

     

    The forumlas provided below work great too