Power Fx in Dataverse: Calculate Duration from Month and Year Columns

In this #PowerShot, I will walk you through creating logical expressions to accurately compute the duration in a Dataverse Formula column using Power Fx. These calculations rely on the end year, end month, and start year, start month columns. No complete date column is used in this calculation. This approach is helpful when you need to implement logic without having absolute dates in the data.

---

Let's Get Started

Initially, it's crucial to grasp the significance of duration calculations. Dataverse formula columns don't inherently support duration calculations if a table doesn't have date type data for the calculation. When only end year, end month, start year, and start month columns are present, users can't depend on reference data. These calculations are particularly useful for scenarios such as reporting or improving data presentation on a form or view.

Now, let’s delve into the specifics of how to construct these logical expressions using Power Fx, the formula language used in Canvas Power Apps and Dataverse Formula Columns. Power Fx allows users to create dynamic and flexible formulas that can manipulate data effectively. The first step involves accessing the Formula column in your Dataverse table where you want to implement these calculations. Once you are in the Formula column editor, you will be able to input your logical expressions.

---

Initial Setup

To implement a clean data setup, we need to have a Month and an Year table in Dataverse with required columns so that this data can be referenced in the main table. Below is a sample setup of these two Dataverse tables with sample data.

Table Name: btd_year

Column Name	Schema Name	Data Type	Purpose
Name	btd_name	Single Line of Text	To store the year value in text format

Data for Year table:

List of consecutive years from 2015 to 2025 displayed in a table format, sourced from a Dataverse year table.

Table Name: btd_month

Column Name	Schema Name	Data Type	Purpose
Name	btd_name	Single Line of Text	To store the month name
Month Value	btd_monthvalue	Single Line of Text	To store month value in text format

Data for Month table:

Table displaying month numbers alongside their corresponding abbreviated names from a Dataverse month table.

In order to implement the calculated fields for the duration, we need to have the 4 data columns (end year, end month, start year and start month) in the Dataverse table on which we can implement the calculation. Below is a sample setup of a Dataverse Table with the 4 columns:

Table Name: btd_returns

Column Name	Schema Name	Data Type	Purpose
End Year	btd_endyear	Lookup	Lookup to Year table
End Month	btd_endmonth	Lookup	Lookup to Month table
Start Year	btd_startyear	Lookup	Lookup to Year table
Start Month	btd_startmonth	Lookup	Lookup to Month table

---

Calculating Duration

Below is the schema and formula for the Duration column:

Column Name	Schema Name	Data Type	Formula
Duration	btd_duration	Formula (Single Line of Text)	Concatenate(Text(Int((If(!IsBlank('End Year'),('End Year'.Name*12+'End Month'.'Month Value'),Year(UTCNow())*12+Month(UTCNow())) - ('Start Year'.Name*12+'Start Month'.'Month Value'))/12),"0")," year(s) and ", Text(Mod((If(!IsBlank('End Year'),('End Year'.Name*12+'End Month'.'Month Value'),Year(UTCNow())*12+Month(UTCNow())) - ('Start Year'.Name*12+'Start Month'.'Month Value')),12),"0")," month(s)")

Power Fx Formula for calculating duration in years and months based on data in Dataverse

Explanation:

If we break down the above formula, and let's assume, End Year = A, End Month = B, Start Year = C and Start Month = D

We first calculate the Year difference by using:

INT(((A*12+B) - (C*12+D))/12)

and then calculate the Month difference by using

MOD(((A*12+B) - (C*12+D))/12)

Finally, the result is concatenated with the string as shown in the formula.

One thing to note in the formula is, in case the End Year and End Month are not provided by the user, the UTCNow() function is used to get the current month and year to facilitate the calculation. If this is not a requirement in your case, you can get rid of the If condition and the UTCNow() logic.

---

Setup Results

Below is a screenshot from the table in Dataverse where we can see that the start year, start month, end year and end month are provided by the user and the Duration column is calculated based on the logic implemented using the formula above.

Table displaying calculated duration of returns, showing start and end dates with corresponding durations ranging from several months to over ten years.

---

By implementing the logic outlined in this post, you will be fully prepared to calculate duration effectively within your Dataverse Formula columns. This functionality not only improves your data manipulation capabilities but also significantly enhances your analytical processes. Whether you are managing project deadlines, tracking performance metrics, or analyzing time-related data, these duration calculations will ensure precision and streamline your reporting and data management efforts.

I hope you found this interesting and it helped you. Thank you for reading!