Make a measure Measure difference of dates but ignore nulls

[MarioScapellato]

I have two dates I would like get the differnece of. One is [First Response] the other is [Created]. In some cases [First Response] is null. When [First Response] is null i would like to ignore it from the calcualtion of the measure. 

I've tried writing this measure several different ways. Mostly using CALCULATE. But i usually end up with a "Invaild OADate value" error. I even tried making a condinal column that says TRUE if [First Response] is has a value or FALSE if the [First Response] is null and using this measure

Measure =

CALCULATE(sum('Ticket Table'[First Response]),

'Ticket Table'[First Response Has Date]= "TRUE")

- sum('Ticket Table'[Created])

This still gave me an Invaild OADate value. 

[MFelix · Accepted solution]

Hi @MarioScapellato,

You should do a SUMX to make the calculation. You cannot sum dates you need to calculate the difference and then sum.

It should look something like this.

Days =
SUMX (
    Table1;
    IF (
        Table1[First Response] = BLANK ();
        0;
        Table1[First Response] - Table1[created]
    )
)

Regards,

MFelix

[MarioScapellato]

thanks @MFelix

There's one more issue i'm having though. I'm unable to change this measure to be in a date time format. The end goal is to show this as HH:mm. It appears that using a sumx doen't allow me to change the data type to date/time. Do you know of a way around this?

[MFelix]

Try to add the following measure:

Measure = (TRUNC([Days];0)) & " " & FORMAT(([Days]-TRUNC([Days];0)*60);"hh:mm")

Not sure if it's ok on calculation.

Regards,

MFelix