Replace empty record with current date

[njxfoster]

Hi Guys,

I am creating a speed to lead column by using Formula ( First Contact - Adjusted Created Time)

However there are some users who doesn't have first contact yet, so the record will be empty. and result will become-64100000.00

 I want to set these empty record to current date so after calculation it can become an aging indicator

How can I replace these empty First Contact with current date without affecting other records?

Thanks in advance

[njxfoster]

Hi Guys,

I am creating a speed to lead column by using Formula ( First Contact - Adjusted Created Time)

However there are some users who doesn't have first contact yet, so the record will be empty. and result will become-64100000.00

 I want to set these empty record to current date so after calculation it can become an aging indicator

How can I replace these empty First Contact with current date without affecting other records?

[v-cazheng-msft]

Hi @njxfoster 

May I know whether your issue has been resolved? If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. Thanks in advance!

Best Regards,

Community Support Team _ Caiyun

If you still have problems on it, please feel free to let us know. Thanks a lot!

[VahidDM]

Hi @njxfoster 

speed to lead =
VAR _D =
    IF ( ISBLANK ( [First Contact] ), TODAY (), [First Contact] )
RETURN
    _D - [Adjusted Created Time]

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[njxfoster]

Hi @VahidDM 

Thank you for your answer!

I have trouble running your DAX. The date comes out wicked. How can I solve this problem?

[VahidDM · Accepted solution]

Hi @njxfoster 

Try this:

speed to lead =
VAR _D =
    IF ( ISBLANK ( [First Contact] ), NOW(), [First Contact] )
RETURN
   DATEDIFF([Adjusted Created Time], _D, MINUTE )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/