cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MarioScapellato Frequent Visitor
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
Super User

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

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



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

Proud to be a Datanaut!




3 REPLIES 3
Super User
Super User

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

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



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

Proud to be a Datanaut!




MarioScapellato Frequent Visitor
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
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!