How to Use Formula Columns in Microsoft Dataverse

While working in Microsoft Dynamics 365 customer relationship management (CRM) applications, you may have noticed a warning message, like the one below, when trying to create a “calculated” column. This is because Microsoft is transitioning away from traditional “calculated” columns and instituting “formula” columns. Key differences between calculated columns and formula columns in Microsoft Dataverse lie in how and when the values are computed, and what language is used to define them.

Click here to open image in a new tab

The transition from calculated columns to formula columns in Dataverse is significant for several reasons, especially for developers, data modelers, and business users working with Microsoft Power Platform and Dynamics 365.

Formula columns let you automatically calculate values in a Dataverse table using simple expressions. They work like formulas in Excel and are written using Power Fx, a user-friendly language designed for low-code development. As you type, IntelliSense (a code-completion aid from Microsoft) provides suggestions to help guide you through building your formula, making it simpler to create logic without needing traditional programming skills.

Examples of Formula 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 into the quality of a given lead
Follow Up By: Follow up on an activity within a specified number of days based on priority

Formula columns use the following data types:

Text
Decimal Number
Whole Number
Float
Boolean Choice (Yes/No)
Choice (formerly Option Sets)
DateTime

Note: The currency data type isn’t currently supported (as of 11/19/2024).

These are the operators available in a formula column:

Add: +
Subtract: -
Multiply: *
Divide: /
Belonging to a collection or table: in and exactin
String concatenation: &

There are multiple function types:

Decimal
String
Boolean
Choice
DateTime (TZI)
DateTime (User local) – limited to comparisons with other user local values, DateAdd, and DateDiff functions
DateTime (Date only) – limited to comparisons with other date-only values, DateAdd, and DateDiff functions
Currency
Whole Number

Adding a formula column is as simple as adding a column type in Dynamics 365. Here are the steps to follow:

Select “Tables” and then add/select the table into which you want to add the formula column.

Click here to open image in a new tab

Select “Columns” and then click “New column.”

Click here to open image in a new tab

Select data type: “Formula” and enter the following information:

Display name
Formula

Click here to open image in a new tab

The “Data type” for the column will update to the relevant type after you write a formula into the text interface. Note: Once saved, the data type of a column cannot be changed.

Click here to open image in a new tab

Select any additional properties, like “Searchable,” if you want this column to be available in views, charts, dashboards, and Advanced Finds.

Lastly, select “Save.”

Viewing Formula Columns on an Opportunity Record

The new formula column can be used in the same way as any other column in Dynamics 365. When added to a form, the formula column will be read-only, and display data based on the input parameters.

Click here to open image in a new tab

In summary, formula columns provide enhanced functionality and can be used to surface key data quickly, helping your team make informed decisions.

Now that you’ve explored how to use formula columns in Microsoft Dataverse and Dynamics 365 CRM applications, what other challenges can we help you solve?

As a top 1% Microsoft Business Applications Partner, the Microsoft team at Forvis Mazars can help you enhance your CRM system’s flexibility and performance. Connect with us today to get started on your next CRM project.

Related FORsights