Days between dates column not correctly computing

[svishwanathan]

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[SUBMITTED_DATE])*1.)

Lapse Days=Time to Close RFIs = IF([ANSWERED_DATE]> [Required Date],[ANSWERED_DATE]-[Required_DATE])*1.0,Blank())

[Anonymous]

Have you tried using the DATEDIFF function instead?

https://msdn.microsoft.com/en-us/library/dn802538.aspx

[svishwanathan]

I tried but doesnt help

Time to Resolve = SWITCH(TRUE(),RFIs[PMSCRFI_REQUIRED_DATE]<RFIs[PMSCRFI_ANSWERED_DATE],-1*DATEDIFF(RFIs[PMSCRFI_REQUIRED_DATE],RFIs[PMSCRFI_ANSWERED_DATE],DAY),RFIs[PMSCRFI_REQUIRED_DATE]>RFIs[PMSCRFI_ANSWERED_DATE],DATEDIFF(RFIs[PMSCRFI_ANSWERED_DATE],RFIs[PMSCRFI_REQUIRED_DATE],DAY),0)

[svishwanathan]

[Yggdrasill · Accepted solution]

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]

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

[Anonymous]

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.

[Anonymous]

Can you share some sample data, what result you are getting and what result you are expecting?