Calculating Upcoming Dates in Dynamics 365

Calculating Upcoming Dates in Dynamics 365

A good CRM is about more than just having a sleek system for your employees. It should be a tool used to increase customer satisfaction. Efforts to show that you value your customers go a long way to retain loyalty. This is known as increasing your customer’s “lifetime value”, referenced in this article by Entrepreneur Magazine. One easy way to develop relationships with your customer is to send them an email on their birthday or for an anniversary. Microsoft’s CRM software can easily cater to this strategy.

Date fields in Dynamics 365 are a great way of storing historical information regarding a Contact, Lead, or Account. However, performing date calculation greatly increases their power and allows for more robust data analysis.

Currently, in CRM, you can’t query based on a specific month in the field. For example, you can’t query all contacts whose birthday occurs in the month of October because the advanced find always takes the whole date into account. Fortunately, there is an OOB way around this. This article will explain how to calculate upcoming anniversaries or birthdays in CRM.

Create “Date of Birth” Field

Create a new field on the Contact entity titled “Date of Birth”. The Behavior setting of the field must be User Local, which is why I am not using the OOB Birthday field. The OOB Birthday field Behavior equals Date Only, which will not allow us to do the date calculation necessary.

Create “Age” Calculated Field

Create another field on the Contact entity titled “Age”. This field should be of Data Type “Whole Number” and Field Type “Calculated”. Below is the calculation:

Create “Upcoming Birthday” Calculated Field

Create a final field called “Upcoming Birthday”. The Data Type should be “Date and Time” with a Field Type of “Calculated”. Below is the calculation for this field:

Add Fields to Form

After this, put the Date of Birth, Upcoming Birthday, and optionally the Age fields on the Contact form. At this point, copy over data from the OOB Birthday field to the new one via a workflow.

Potential Use Cases

You can now create advanced finds to show you who in your database has a birthday in the next week, month, etc. You can also build workflows that reference the Upcoming Birthday field and send automated emails to people on their birthdays. Furthermore, you can use the Age calculated field to your advantage by creating marketing lists that are segmented by specific age. You could also use these methods to celebrate upcoming anniversaries of when a client partnered with your business.

For a reference on how to do this customization for CRM 2015/2016, as well as additional information, please refer to this blog post.

By | 2018-04-16T15:40:40+00:00 February 12th, 2018|Uncategorized|4 Comments

About the Author:

Philip Frederick
Philip is an experienced CRM System Administrator and IT Business Analyst. As a TrellisPoint Technical Consultant, he provides clients with expert CRM system consulting and training that enables business results.

4 Comments

  1. Kyle October 11, 2018 at 5:47 pm - Reply

    Problem with this solution is that the Current Age field will round up to the nearest whole number.

    Example:

    Date Of Birth: November 01, 2016

    Current Date: October 01, 2018

    Age (using DiffInYears logic above) will be 2 years (i.e. 2018 – 2016 = 2)

    …This person however is only 1 (i.e. hasn’t yet had their 2nd birthday).

    …This also causes the Upcoming Birthday field to be incorrect, as it uses incorrect data in the Age field in its calculation.

    • Philip Frederick
      Philip Frederick October 12, 2018 at 4:10 pm - Reply

      Thanks for your comment! The fact that the Age calculated field rounds up is not an issue here. The Age field is not meant to calculate the current age of the contact (notice that it is named Age and not Current Age). In the blog images, you will note that the Age calculated field is not placed on the form. The reason for that is that it is meant to be a staging value for the main goal of calculating the upcoming birthday field.

      So to use the example you gave, you are perfectly right that the DiffInYears logic would return 2 (2018-2016) for the Age field if the current date is 10/01/2018. However, if we look at the Upcoming Birthday calculated field formula, you will see that the following takes place: AddMonths((Age * 12), DateOfBirth). With Age = 2, and DateOfBirth = 11/1/2016, the formula resolves to this: AddMonths((2 * 12), ’11/01/2016′). The formula then resolves to this: AddMonths((24), ’11/01/2016′). Adding 24 months (2 years) to the value of 11/01/2016 will result in a value of 11/01/2018. This is the correct value of the upcoming birthday for our hypothetical soon-to-be-two-year-old!

  2. Rick November 1, 2018 at 1:27 pm - Reply

    Hi Philip,
    I’ve a question regarding this formula. I’ve set it exactly what you said, and this will works, but there is one issue.

    As an example; Last week was my birthday, there is still 30-10-2018 in the field “Upcoming Birthday”. How can we changed that to 30-10-2019? (
    So, after the date is expired it have been set +12 months..

    Thank you in advance.

    • Philip Frederick
      Philip Frederick December 14, 2018 at 2:59 am - Reply

      Rick,

      It should increment +1 year once 2019 arrives. That will cause the formula to recalculate properly.

Leave A Comment