Introduction to calculation columns
A calculation column lets you define a calculation formula that is run by Microsoft Dataverse regardless of the form that is used to edit or add data. Calculations allow you to improve data integrity and simplify form development. Unlike asynchronous rollups that calculate based on a scheduled job, calculations run in real time when the row is saved.
Calculated columns are powerful, and have the following key considerations:
Calculated columns use the columns from the current table or related parent tables from a many-to-one relationship.
Calculated columns are read-only.
The expression support is available on the current table and the related parent table columns in the Condition sections and the Action sections. The built-in functions include:
ADDHOURS
ADDDAYS
ADDWEEKS
ADDMONTHS
ADDYEARS
SUBTRACTHOURS
SUBTRACTDAYS
SUBTRACTWEEKS
SUBTRACTMONTHS
SUBTRACTYEARS
DIFFINDAYS
DIFFINHOURS
DIFFINMINUTES
DIFFINMONTHS
DIFFINWEEKS
DIFFINYEARS
CONCAT
TRIMLEFT
TRIMRIGHT
You can define branching and multiple conditions. The logical operations that are available include AND and OR operators.
The following column types support calculations:
Text
Option Set
Two Options
Whole Number
Decimal Number
Currency
Date Time
The visual editing capabilities include intellisense when you define the calculation in the Action portion of the column.
You can configure calculated columns to use custom controls.
Calculated column limitations
If the calculated column depends on another value, it will not be recalculated until the row is retrieved.
You cannot use values in calculated columns that reference a related table, another calculated column, or a logical value in the same table to sort data that is returned by a query. Though your query can specify that the results should be ordered by using a calculated column, the sort direction will be ignored and will not throw an error. If the calculated column references only simple values in the same row, sorting works normally.
Only columns from an immediate parent table can be used in a calculated column.
Calculated columns can reference other calculated columns in their formula, but they cannot reference themselves.
Need help? See our troubleshooting guide or provide specific feedback by reporting an issue.