cancel
Showing results for
Search instead for
Did you mean:
Highlighted
MarioScapellato Frequent Visitor

## Make a measure Measure difference of dates but ignore nulls

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.
1 ACCEPTED SOLUTION

Accepted Solutions Super User

## Re: Make a measure Measure difference of dates but ignore nulls

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

3 REPLIES 3 Super User

## Re: Make a measure Measure difference of dates but ignore nulls

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

MarioScapellato Frequent Visitor

## Re: Make a measure Measure difference of dates but ignore nulls

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? Super User

## Re: Make a measure Measure difference of dates but ignore nulls

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!