Microsoft Dynamics 365 for Sales
One of the key benefits of Dynamics 365 for Sales is its ability to track details of Sales Won vs individual sales persons budgets. Both sales managers and sales people can easily see how successful they are and what remains to be achieved in the current period. Sales vs Budget is normally tracked within a given period. Some companies want to roll forward all estimated revenue to see the year-to-date objectives rather than just looking at the current period.
Recently I had a requirement from a customer to include all estimated revenue, regardless of close date on the opportunity in the current active goal period in Microsoft Dynamics 365 for Sales. This in effect was rolling over any open opportunities into the current goal period to track estimated revenue.
Goals are commonly defined for a period be it a week, month or quarter etc. The defined rollup fields (within the goal metric) contain the date field used to test for inclusion in the goal period along with the record state e.g. won or lost etc.
Normally, to track estimated revenue the goal rollup field is based on the estimated revenue, status of open and the estimated close date for inclusion in the goal period.
Sales team members would manage the opportunities keeping them updated with the latest status and revision of estimated close date. With the best will in the world not all off the opportunities get updated due to a myriad of reason’s. As a result, when a new goal period starts these older opportunities will excluded from the estimated revenue.
The key to this was twofold. One, a date field was required on the opportunity to always reflect the current date. Two, the goal rollup would need to reference the new date field to test for inclusion in the goal.
To implement the current date for the opportunity I turned to a calculated field. As the name suggests these field types are used to calculate a result based on values in fields on the related entity. One notable property of the calculated field is when the calculation is performed. When the field is addressed/accessed in a view, API, export to excel, opening a form etc. the calculation is executed there and then. This ensures you always have the most current value when address/access the field.
With that in mind I created a new calculated field (see screenshots below) on the opportunity that would return the current system date when addressed/Accessed
The goal rollup fields in question now use the new ‘Current Date Now’ field to determine inclusion in goal period. The date returned will always be the current date and so long as the opportunity is open it will be included in the estimated figures.
Written by Brian MacCullagh, Dynamics 365 Consultant