calculated date difference from different months giving negative value

[Anonymous]

Trying to create a calculated column that takes the difference between 2 different dates.  However I'm running into an issue where the formula is not recognizing the values that are from 2 different months. 

For example:  Wed, June 20 (DoneDate) - Thur, July 12 (ToDoDate) will get me a negative value of -8 rather than giving me 22.

Here is the formula i'm using:

Time = IF(OR(ISBLANK('Table1'[DoneDate].[Day]), ISBLANK('Table1'[ToDoDate].[Day])), 0, DATEDIFF('Table1'[ToDoDate].[Day],'Table1'[DoneDate].[Day], DAY))

Calcuation will work just fine if the dates are in the same month.  

[v-piga-msft · Accepted solution]

Hi @Anonymous,

It seems that you may modify your formula like below.

Time =
IF (
    OR (
        ISBLANK ( 'Table1'[DoneDate].[Day] ),
        ISBLANK ( 'Table1'[ToDoDate].[Day] )
    ),
    0,
    DATEDIFF ( 'Table1'[DoneDate], 'Table1'[ToDoDate], DAY )
)

By my test with Power BI Desktop Version: 2.61.5192.541 64-bit (August 2018), everyhing works as expected.

If you still need help, please share your sample data and your desired output.

Best  Regards,

Cherry

[Anonymous]

Thank you! I didn't realize I was just looking at the [Day] value while doing my calculation.  Works great!