How to Create a Calculated Field in Dynamics 365 (CRM)

You can use Calculated Fields in your Microsoft Dynamics 365 CRM solution, to automate calculations and special processes — without writing a single line of code.

You just add a new field to your entity, define your criteria and logic, add the field to your form, and watch the magic happen! Okay, there might be a few more steps than that, but I’ll explain it all below.

• How to Create a Calculated Field
• Table of Calculated Field Functions
• Difference between Calculated Fields and Rollup Fields
• Limitations of Calculated Fields

If you are on the Dynamics 365 “new experience” user interface, Calculated Fields will be called Calculated Columns.

Note: There are now 5 different Dynamics 365 apps that serve various CRM needs. In some contexts, you may also see those products referred to as “Customer Engagement” or “CE” apps. Calculated Fields work the same no matter which of those apps you’re using.

How to Create a Calculated Field in Dynamics 365

Below are the steps for creating a Calculated Field. You must have the Write privilege on the Field Security Profile entity. If you don’t, ask your sysadmin.

In this example, we want to track annual membership dates. A user will need to be able to enter a Membership Start Date and have the system automatically add one year to this date to give us the Membership Expiration Date.

We already have a field for the start date, but we need to create a new Calculated Field for our expiration date. Both these fields exist on the same entity (in this case, Account).

1. Navigate to the entity and create a new field. (In the new D365 experience, you would navigate to the “table” and create a new “column.”)Click on any screenshot in this article to see a larger version.
2. In Field Type select Calculated.

Enter the following information: Display Name, Name, Data Type, and (for date and time fields) Behavior. The Behavior options are User Local, Date Only, or Time-Zone Independent. You will see an Edit button appear, but here are some important notes before you click on Edit to build your logic:

• Be sure to choose your field Behavior for dates and Format (which determines display options) before clicking on Edit. Once you click on Edit, you cannot come back and change these.
• All DIFF functions require that both the date and time fields have the same Behavior. If they don’t match, an error message will appear indicating that the second field cannot be used in the current function.
3. Now click on Edit.

4. Select the Condition for your Calculated Field and click the checkmark to save it. The Condition is the basic logic of your Calculated Field — “under what conditions” will your field calculate?

   Our logic here is that “under the condition” that the Membership Start Date contains data, we want to add one year to that date and populate our Membership Expiration Date Calculated Field.

5. Select the Action for your Calculated Field. The Action describes what actual calculation your field will do, and based on what inputs.

   In this example, we’ll select the ADDYEARS function. When you select it, it appears with a set of parentheses () next to your function name. Between these parentheses is where you insert the inputs for the function. In this case, we entered (1, new_membershipstartdate).

   • We enter a “1” because we want to add ONE year to our date field.
   • We enter (or select from the drop-down list) “new_membershipstartdate” because that’s what we are adding the one year to: our Membership Start Date field. Note that the schema name of your field is what you will see in this drop-down list, but your final action will show your field label.

   

6. Click the checkmark to save your action and you can see our complete criteria for our new Calculated Field:
7. Click Save and Close. Then be sure to publish your new field in your solution (otherwise it won’t be available for use).
8. Click on Save and Close again to save the modifications made to the field:
9. Click on the Forms tab of the Account entity, located to the left of your screen:
10. Select the relevant form. In this example, I will select the Account form to add the membership expiry date field:
11. Drag and drop the new field from the Field Explorer to wherever you would like it to appear.
12. Click on Save to save the modification to the form:
13. Click on Publish when you are ready to have the new field visible in the form for your users.

As you can see in the screenshot below, I entered a date into the Membership Start Date. When the change was saved to the record, our new Calculated Field automatically added one year and populated our Membership Expiration Date.

Calculated Field Functions

The table below gives you a quick look at all the functions you can use with Calculated Fields.

The “data types” column shows what field types you need to have in order to use that function.

Function Syntax	Data Types	Description	Return Type
CONCAT	One or more single lines of text	Returns a string that contains the first single line of text followed by the second.	String
TRIMLEFT	Single line of text + whole number	Returns a string that is the same as the single line of text but without the first X characters.	String
TRIMRIGHT	Single line of text + whole number	Returns a string that is the same as the single line of text but without the last X characters.	String
ADDDAYS	Whole number + date and time	Returns a new date and time that is equal to the given date and time, plus the specified number of days.	Date and Time
ADDHOURS, ADDWEEKS, ADDMONTHS, ADDYEARS	Same as ADDDAYS	Same as ADDDAYS, but adds hours, months, etc., instead.	Same as ADDDAYS
SUBTRACTDAYS	Whole number + date and time	Returns a new date and time that is equal to the given date and time, minus the specified number of days.	Date and Time
SUBTRACTHOURS, SUBTRACTWEEKS, SUBTRACTMONTHS, SUBTRACTYEARS	Same as SUBTRACTDAYS	Same as SUBTRACTDAYS, but subtracts hours, months, etc., instead.	Same as SUBTRACTDAYS
DIFFINDAYS	Date and time + date and time	Returns the difference in days between two “date and time” fields. If both dates and times fall on the same day, the difference is zero.	Whole Number
DIFFINMINUTES, DIFFINHOURS, DIFFINWEEKS, DIFFINMONTHS, DIFFINYEARS	Same as DIFFINDAYS	Same as DIFFINDAYS, but finds the differences in minutes, hours, etc., instead.	Same as DIFFINDAYS

What’s the Difference between Calculated Fields and Rollup Fields?

Calculated Fields are based on data within a single record or its parent, whereas Rollup Fields can be based on data from many records.

Calculated Fields have many date-related functions that Rollup Fields lack. Also, the two have a few different limitations.

So, if you want a field to display the end date of an individual account’s 1-year subscription, that’s a great use for a Calculated Field. If you want to display the total estimated revenue of all open opportunities, you need a Rollup Field instead.

To learn more about Rollup Fields, please see our blog, How to Create Rollup Fields.

Limitations of Calculated Fields

Calculated fields are a great customization feature of Dynamics 365. However, there are some limitations and other things you should consider before you dive in.

• Calculated fields are read-only.
• A Calculated Field can span only two entities (current entity + parent record).
• You cannot trigger workflows or plugins on Calculated Fields.
• The maximum number of chained Calculated Fields is 5.
• A Calculated Field cannot refer to itself or have cyclic chains.

---

For details and examples based on the new experience (where Calculated Fields are called Calculated Columns), see Microsoft’s page here

You may also want to read our articles about other special kinds of fields: Multi-Select Option Sets and Rollup Fields.

If you have any questions about creating fields or otherwise tailoring the D365 system to your business, reach out to us.