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