topic Replace empty record with current date in DAX Commands and Tips

Replace empty record with current date

njxfoster — Thu, 27 Jan 2022 23:19:55 GMT

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?

Re: Replace empty record with current date

VahidDM — Fri, 28 Jan 2022 02:00:28 GMT

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/

Re: Replace empty record with current date

njxfoster — Fri, 28 Jan 2022 16:07:22 GMT

Hi VahidDM

Thank you for your answer!

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

Re: Replace empty record with current date

VahidDM — Mon, 31 Jan 2022 00:18:09 GMT

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/

Re: Replace empty record with current date

v-cazheng-msft — Wed, 02 Feb 2022 05:22:53 GMT

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!