Formula columns have been available in Dataverse for a few years now, and Microsoft has made it clear that they are the preferred option going forward. But does that mean calculated columns are obsolete? Not quite. There are still a few scenarios where a calculated column may be the better choice. Let’s look at when it still makes sense to use one.

The message we see inside of the Maker Portal when we add a new column and specify behavior to be calculated.

1.   The output needs to be a currency column

This is the biggest reason why you will need to use a calculated column. Formula columns can use currency fields in a formula by converting them with Decimal(), but a formula column itself still cannot have a currency data type.

Let’s say we want to calculate a weighted pipeline value on an Opportunity.

Weighted Pipeline Value = Estimated Revenue × Probability / 100

If I need this new column to be a currency column in Dataverse, a calculated column is the better choice. This is especially important if the value will appear in views, reports, integrations or other places where I want Dataverse to treat it as money, not just a decimal number.

Building a calculated field to calculate weighted pipeline value as a currency output.

2.   You need different null behavior

Here’s one I found by mistake and later verified with Microsoft documentation. Suppose you have:

• Field A = blank
• Field B = 2
• Field C = 3

A formula column will evaluate this as: blank + 2 + 3 = 5. Null is treated as a zero during the calculation.

A calculated column behaves differently: blank + 2 + 3 = blank.

Which one is correct? That depends entirely on the business requirement. If a missing value should mean “I don’t have enough information to calculate this yet,” you should use a calculated column.

3.   Formula columns don’t support a field type or format you need

Formula columns have come a long way, but there are still column types and formats they cannot use. (There’s a complete list of known issues here in Microsoft documentation.)

I ran into this recently with the standard Email column on a Contact. Email looks like a normal text field, but in Dataverse it is a string column with an email format. Formula columns do not currently support string columns formatted as email.

So something that seemed simple, like “Mark the Contact Profile as Complete when Email, Business Phone and Job Title contain data” hit a limitation almost immediately since I was referencing email in the Power Fx formula.

This doesn’t mean a calculated column will solve every unsupported formula column scenario. You still need to verify that the fields and functions you need are available in the calculated column designer. But it’s a good reminder that when formula column doesn’t support a field you need, you can still consult the calculated column option before you head to Power Automate or custom development as your solution.

The real-world example where I found this email format limitation inside of a formula column.

Which should I use?

When you are building a new Dataverse column, my overall recommendation (and Microsoft’s as well) is to first evaluate if you can solve it cleanly with a formula column. If so, great! Use formula columns.

If not, take a look at the requirement before reaching for something more complicated. A calculated column may be the right choice when you need:

• a currency output
• the null behavior of calculated columns
• functionality involving a column that formulas do not currently support

Formula columns may be the modern option, but calculated columns are still hanging around for a reason. Sometimes the old tool is still the right tool.