Replacing NOW() with blank dates to calculate days difference

[Anonymous]

Hi All,

I am trying to calculate the days difference between two dates e.g. create and resolved some tickets are not resolved hence the dates are blank so to handle that i created "Resolved 2" however i use this in the "Incident Age 2" formula its given me wierd result (see below) not sure why. For the dates which are not blank the result is correct. What am i doing wrong? 

NOTE: i cannot use DATEDIFF because it gives output in integer and i need it in float.

Formula to handle blanks

Resolved 2 = IF(vw_incident[resolved_at]=BLANK(),NOW(),vw_incident[resolved_at])

Formula to calculate age

Incident Age 2 = IF(MIN(vw_incident[resolved_at])=BLANK(),1* [Resolved2]-MIN(vw_incident[create_date]),1*

MIN(vw_incident[resolved_at])-MIN(vw_incident[create_date]))

Output:

[mahoneypat · Accepted solution]

Note you can use DATEDIFF to return the result in hours or minutes too, and then divide by 24, etc. to get days in decimal form.  You could also use your Resolved 2 expression in a variable and use that in your Age measure (instead of a separate measure).

Regards,

Pat

[Anonymous]

@mahoneypat  i just noticed a slight difference in the result , please see below. The result for the below example should 8.95 but with our calculation its coming up 8.96 what could be wrong ?

[mahoneypat]

Looks like a rounding error.  Maybe return MINUTES in the DATEDIFF instead?

Regards,

Pat

[Anonymous]

@mahoneypat  i returned it in SECONDS and then divided by /24/60/60 to convert it in days and it solved the issue.

[Anonymous]

@mahoneypat  thanks for this, it was the simplest solution and it worked for me. Kudos to you.