PowerObjects Blog 

for Microsoft Business Applications


Enhancements to Calculated Fields and Rollup Fields in CRM 2015


Enhancements to Calculated Fields and Rollup Fields in CRM 2015

The initial release of Microsoft Dynamics CRM 2015 introduced the out-of-the-box functionality of Calculated Fields and Rollup Fields, and that functionality is now enhanced with the 2015 Spring Release!

Date Difference Calculations

Calculated fields will now be able to calculate the difference between two date fields in minutes, hours, days, weeks, months, or years. To illustrate how the date difference calculations work, let's look at a scenario where we want to calculate how long it takes between a lead coming into the system and when contact is first initiated with that lead.

First, we'll create a date field called Initial Contact Date. Then, we'll create a field called # of Days Before Contact whose data type can be calculated, such as whole number or decimal. Set Minimum and Maximum values appropriately, choose Calculated for Field Type, then click the Edit button.

We'll set a condition to calculate if the Initial Contact Date contains data. For our calculation action, we'll start typing "DIFFIN…", which will bring up the following options:

For our purposes, we'll do our calculation in days. Because we're comparing when a lead enters the system to when that lead is initially contacted, we'll use Created On as our start date and time and Initial Contact Date for our end date and time. Our calculated field will end up looking like this:

With the Spring Release, determining the difference between two dates is now little harder when it comes to creating an appropriate field and selecting the relevant date fields!

The Now() Function

To complement the Date Difference calculations in the Spring Release, there's another function in Calculated Fields called "Now()", which populates the current date/time value in a field or a formula. The Now() function is a little selective in how it interacts with another new concept also rolling out in the Spring Release: Behavior of date fields whose choices are User Local, Date Only, and Time-Zone Independent.

For our purposes, we'll only delve into date field Behavior inasmuch as it interacts with the Now() function. Here are some things to keep in mind:

  • As of publication time, you can only use Now() directly in a Date Difference formula if the Behavior of the date field is set to "User Local"

A Date Difference formula comparing a date field with User Local Behavior and Now() might look like this:

 

  • You can't directly use Now() in a formula where the other date field has a Behavior of Date Only, but if you create a new date field, set its Field Type to Calculated and its Behavior to Date Only…

…you can use the Now() function on its own to populate the field…

 

…and then use that field in a calculation.

  • So far, the Now() function doesn't work at all—either in a Date Difference formula or used on its own to populate a field—with date fields whose Behavior is Time-Zone Independent.

Rollup Fields – Aggregate on the Activity Pointer

After diving in deep with the Date Difference calculations, we'll end our discussion of the enhancements coming to Calculated and Rollup Fields to briefly note that the Spring Release will now allow you to aggregate on the Activity Pointer. In the initial release of CRM 2015, you could roll up individual Activity types (Appointments, Tasks, Emails, etc.) to other entities, but the Spring Release will now allow you to roll up all Activities and filter from the top-down.

 

We hope this blog was useful to you. Check out our main Dynamics CRM 2015 page for even more tips and tricks!

Happy CRM'ing!

Joe CRM
By Maria Valley

Leave a Reply

Your email address will not be published. Required fields are marked *

5 comments on “Enhancements to Calculated Fields and Rollup Fields in CRM 2015”

  1. Hello Joe,
    I'm having problems on following your steps by creating an Age field.
    First of all, I can't use NOW() by Current Date. An error "You can only use a Date Only type of field" appears, though the Behavior of a field is "Date Only" (see screenshot). How did you manage to do that in your example?
    Anyway, I tricked here a little: firstly, set Behavior as User Local, then set to NOW() and then changed Behavior to Date Only. It caused no errors. But then the second problem appears by the field Age (whole number). I get an error on using the DIFFINYEARS-function: "You can't use currentdate, which is of type dateTime, with the current function" (see screenshot).
    Have you any ideas why is it working for you and not working for me? Are you able to repeat your described steps now as well or do you now get errors too?
    Thanks in advance and best regards
    Nadezda

    1. To set the 'Current Date' field, try using the ADDDAYS function. I used, ADDDAYS(0, NOW()). it worked, didn't give me any errors.

      I want to use this field in a Roll-Up to calculate prior year revenue for a customer, through todays date, last year...but the field doesn't show up as a filter option when creating the rollup. Extremely frustrating.

  2. How can I a use information from a Customer service schedule? For example, how to calculate date and time of some event in custom entity, not just adding hours, but adding the time of working hours from Customer service schedule?

  3. For the SET AGE, I am having an issue. The fact that we can only use whole number to calculate diffinyears, months or days, ends up with CRM rounding up the number. An example of the problem is: Someone born in 08-28-2000 shows as 16 years old even though the date today is 03-01-2016. The person is 15.51 years old not 16. This is because the roundup factor will move anything from .51 to the ceiling number. Any ideas on this issue?

  4. Has anyone tried a calculated which shows the latest appointment date before todays date? I am bit curious on how to set that up.

PowerObjects Recommends