Forum Discussion

njxfoster's avatar
njxfoster
Icon for Helper I rankHelper I
4 years ago
Solved

Replace empty record with current date

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
  • VahidDM's avatar
    VahidDM
    4 years ago

    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/

     

     

4 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      njxfoster
      Icon for Helper I rankHelper I
      4 years ago

      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's avatar
        VahidDM
        Icon for Super User rankSuper User
        4 years ago

        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/

         

         

  • v-cazheng-msft's avatar
    v-cazheng-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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!