Calculated fields were first introduced to Microsoft Dynamics 365 CRM years ago, but the value they can provide is timeless.
This field type can be set up to provide formulas and calculations at the field level, similar to Microsoft Excel. You can use calculated fields in Microsoft Dynamics to automate calculations and special processes—without writing code.
Here are a few examples of calculated fields:
- Weighted Revenue: Estimated revenue multiplied by probability
- Net Worth: Assets subtracted by the liabilities for a given account
- Cost of Labor: Base rate up to 40 hours, plus additional overtime
- Contact Number: Phone number for an opportunity based on account or contact
- Lead Score: Single field that provides insights to the quality of a given lead
- Follow Up By: Follow up on an activity by a specified number of days based on priority
When setting the value in a calculated field, the following math operators are available:
- = (Equal)
- + (Plus)
- – (Minus)
- / (Divide)
- * (Multiply)
The following string functions are available for single-line fields:
- TRIMLEFT (trims characters from the left to right)
- TRIMRIGHT (trims characters from the right to left)
- CONCAT (adds single lines of text together)
The following date/time functions are available for whole number or date/time fields:
- ADDHOURS
- ADDDAYS
- ADDWEEKS
- ADDMONTHS
- ADDYEARS
- SUBTRACTHOURS
- SUBTRACTDAYS
- SUBTRACTWEEKS
- SUBTRACTMONTHS
- SUBTRACTYEARS
- DIFFINMINUTES
- DIFFINHOURS
- DIFFINDAYS
- DIFFINWEEKS
- DIFFINMONTHS
- DIFFINYEARS
- NOW (gives current date and time)
In the scenario below, we’ll set up a calculated field in Microsoft Dynamics to display the number of days that a case has been open.
How to Set Up Calculated Fields in Microsoft Dynamics 365 CRM
- Navigate to make.powerapps.com and open the Default Solution or select the solution in which you want to work:
- Select the Case under Tables in the navigation pane under Objects:
- Click New and select Column:
- Specify the Display name, then Data type as Number – Decimal (as shown in the screenshot below). Using a decimal field allows you to set the precision you want. For a whole number, set the precision to zero.
- Set the Behavior to Calculated, then click Save and edit (a pop-out screen to enter the calculation for the field will open):
- Add a condition for Status = Active and the Action to Number of Days Open to DIFFINDAYS(Created On, Now()):
- Click Save and Close to exit the screen above and then click Save and Close again to exit the field set up screen.
- Add the field to your form and be sure to Save and Publish.
Here is the field on the case record:
Now that you’ve seen how to use calculated fields in Microsoft Dynamics 365 CRM, what other challenges can we help you solve?
The Business Technology Solutions team at FORVIS is a Microsoft Dynamics Gold Partner and assists clients by providing analysis, design, implementation, upgrades, training, and support services for customer relationship management (CRM) and enterprise resource planning (ERP) systems. Use the Contact Us form below to send us a message.