Introduction to calculated columns

  • 6 minutes

A calculated 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. You can use calculated columns to automate otherwise manual calculations. 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:

    • Single line of text

    • Choice

    • Yes/no

    • Whole Number

    • Decimal Number

    • Currency

    • Date and 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.

Common Usage Scenarios

  • 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 column 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

Calculated column limitations

  • If the calculated column depends on another value, it will not be recalculated until the row is retrieved.

  • You can't 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 won't throw an error. If the calculated column references only simple values in the same row, sorting works normally.

  • Only columns from the table and columns from tables in a many-to-one relationship can be used in a calculated column.

  • Calculated columns can reference other calculated columns in their formula, but they can't reference themselves.