CRM 2015 Update 1 Highlights – DiffinDays Calculated Field Update

I want an easy way to calculate the average time an opportunity is open for by sales person without writing any code.

Pre CRM 2015 Update 1, I would have to write some code to calculate the difference in days between created on and actual close date, then use a chart to display average for the sales person.

With the latest CRM Online release update 1 a new calculated field formula has been added “DiffinDays” this allows me to calculate the difference between two dates or difference between a date and Now.

So to solve my problem:

  1. Open up the solution file
  2. Navigate to the Opportunity entity -> Fields
  3. First of all I have to create a new field to store the actual close date, with the date behaviour set to User Local to match that of the created on date. The out of the box Actual close date is set to Date only which makes it incompatible with created on.
  4. Create a new field named “Number of days to close” as a whole number or decimal.
  5. Click on the field type and select Calculated
  6. In the designer, create the following rule, you can use the intelisense to type ahead
  7. calculated rule
  8. Save and the new field to a view and to the form optionally
  9. Now you will need to create a workflow to populate your new date field, simple check for actual close date to be populated and then update the new actual close date.
  10. Save and Publish
  11. Now navigate to the opportunity view for closed opportunities
  12. Create a new chart as follows:
  13. average days chart
  14. You now have the average open days for won and lost opportunities by sales person

As a note the image below depicts the entire set of Diff functions:

diffindays

Advertisements

5 Responses

  1. Hi Steve. Did you see the NOW operator that’s also available in Update1? Finally now one have an efficient way of calculating age in a calculated field by choosing the person’s date of birth as the start date and time, the NOW operator as the end date and time – then using the DiffInYears to calculate the age.
    Love it!!

    • Yes true, very nice. Would be interesting to understand the potential performance impact of that, but definitely possible now. The Now() is certainly an interesting concept i.e. you could have a view showing open time by opportunity, to see which opportunities are long running and directing focus. Lots of uses 🙂

  2. […] So if you want to see the length of time that a record, like a case, has been open, you now can do it without any custom code. For more information, read Steve Foster’s blog. […]

  3. […] So if you want to see the length of time that a record, like a case, has been open, you now can do it without any custom code. For more information, read Steve Foster’s blog. […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s