" MicromOne: How to Create Calculated Datetime Fields in Dataverse Using Power Fx Formulas

Pagine

How to Create Calculated Datetime Fields in Dataverse Using Power Fx Formulas

 When working with Microsoft Dataverse, it’s common to need fields that automatically calculate values — for example, a due date, a follow-up reminder, or a time difference between two events.

Traditionally, Dataverse provided the “Calculated” column type for such use cases. However, when dealing with datetime fields, there’s now a better and more flexible approach:
Use a “Formula” column and write your logic using Power Fx.

Why You Should Use a Formula Column Instead of a Calculated Field

While the “Calculated” column type is still available, Microsoft is encouraging makers to move toward Formula columns. The reason is simple:
Formula columns use Power Fx, the same low-code expression language used in Power Apps, Power Automate, and other parts of the Power Platform.

Here’s what that means for you:

  • More flexibility – you can use complex logic, conditions, and even reference related tables.

  • Better performance – formulas are optimized and evaluated more efficiently.

  • Consistency – you can use the same syntax you already know from Power Apps.

  • Dynamic updates – formula columns update automatically when source data changes.

Creating a Datetime Formula Column in Dataverse

Here’s how you can create a formula column to handle datetime calculations:

  1. Go to your table in Dataverse (via Power Apps → Data → Tables).

  2. Click + New column.

  3. Set:

    • Data type: Formula

    • Return type: Date and Time

  4. Click Edit formula to open the Power Fx editor.

  5. Write your formula.

Example 1: Add Days to a Date

If you want to calculate a follow-up date 7 days after a record is created:

CreatedOn + Time(7, 0, 0, 0)

(Adds 7 days to the CreatedOn date.)

Example 2: Calculate Duration Between Two Dates

If you want to find the number of days between a “Start Date” and an “End Date”:

DateDiff('Start Date', 'End Date', Days)

Example 3: Conditional Datetime Formula

If you only want to calculate a deadline when a record’s status is “Active”:

If(Status = "Active", CreatedOn + Time(3,0,0,0), Blank())

You Can Also Use Sort() on Related Columns

One of the great advantages of Power Fx Formula columns is that you can now sort and filter data from related tables directly in your formula — something not possible with legacy calculated fields.

For example, if you have a related table called ‘Tasks’, and you want to retrieve the most recent Due Date related to a project:

Last(Sort(Tasks, 'Due Date', Descending)).'Due Date'

Or, to get the earliest scheduled date:

First(Sort(Tasks, 'Scheduled Date', Ascending)).'Scheduled Date'

This makes formula columns extremely powerful — you can pull in and calculate values from related records dynamically, without needing workflows or plugins.

Best Practices

  • Always specify whether your datetime should be User Local or UTC.

  • Use DateDiff(), DateAdd(), and TimeValue() for accurate calculations.

  • Keep formulas readable — complex Power Fx formulas can be split across multiple lines.

  • Test your logic directly in Power Apps to ensure the expected behavior.

  • Use Sort(), Filter(), and LookUp() with related columns to build smarter data models.