Start of Month and End of Month in Dataverse Formula Columns

In this #PowerShot, I will guide you through the process of building logical expressions that will allow you to accurately calculate the Start of Month and End of Month values in Dataverse Formula columns. These calculations will be based on a regular date column that you may already have in your Dataverse table. Understanding how to manipulate date values is crucial for anyone working with data in Dataverse, as it enables more effective reporting, analysis, and data management.

---

Let's Get Started

To begin, it’s important to recognize the significance of the Start of Month and End of Month calculations. Dataverse Formula columns do not have support for Start of Month or End of Month functions. These calculations are particularly useful in scenarios such as financial reporting, where you may need to aggregate data on a monthly basis, or in project management, where tracking milestones at the beginning or end of a month is essential.

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

In order to implement the calculated fields for the Start of Month and End of Month, we need to have a date type field on the Dataverse table on which we can implement the calculation. Below is a sample setup of a Dataverse Table with the date column:

Table Name: btd_returns

Column Name	Schema Name	Data Type	Purpose
File ID	btd_name	Auto Number	To generate a user friendly identifier of each record
File Date	btd_filedate	Date and Time (Date Only)	Date only column to store the File Date in the table

Calculating Start of Month

In order to calculate the Start of Month, we will first have to extract the day value from the btd_filedate column. Below is the schema for this column:

Column Name	Schema Name	Data Type	Formula
File Date Day Value	btd_filedatedayvalue	Formula (Whole Number)	Day('File Date')

Here, the Day function extracts the day value from a given date and the data type for this formula type column can either be set as a Decimal or a Whole Number. For the purposes of calculation, in this scenario, it has been set to a Whole Number type.

Below is the schema and formula for the Start of Month column:

Column Name	Schema Name	Data Type	Formula
File Date SOM	btd_filedatesom	Formula (Date Only)	DateAdd(DateAdd('File Date',-'File Date Day Value',TimeUnit.Days),1,TimeUnit.Days)

Here, we are using two DateAdd functions. The first DateAdd function subtracts (focus on '-' in the formula) the Day value from the original date effectively returning the end of month value for the previous month. The second DateAdd function then adds one day to the calculation resulting in the start of month date value.

Calculating End of Month

Once we have the start of month date value, it is straight forward to setup the calculation for end of month date value. Below is the schema and formula for the End of Month column:

Column Name	Schema Name	Data Type	Formula
File Date EOM	btd_filedateeom	Formula (Date Only)	DateAdd(DateAdd('File Date SOM',1,TimeUnit.Months),-1,TimeUnit.Days)

Here, we are again using two DateAdd functions. The first one is being used to add 1 month to the start of month date value and then the second one is used to subtract one day from this calculation resulting in the end of month value.

Note: Date Time formula columns cannot be used in legacy calculated columns in Dataverse. Additionally, when using the Day, Month and Year functions along with the Text function to concatenate strings and form a date, the formula columns do not recognize that as a date output and you will not be able to select date as a datatype.

---

Setup Results

Below is a screenshot from the table in Dataverse where we can see the File Date that is provided by end users through a front end application and the calculated values as based on the formulas that we have setup in the previous section.

Screenshot of a Dataverse table displaying calculated dates for returns based on the original file dates. The table includes columns for File ID, File Date, various calculated date columns, and Return details.

---

By following these steps and utilizing the provided formulas, you will be well-equipped to calculate the Start of Month and End of Month values in your Dataverse Formula columns. This capability not only enhances your data manipulation skills but also adds significant value to your data analysis processes. Whether you are handling financial records, project timelines, or any other time-sensitive data, these calculations will help ensure accuracy and efficiency in your reporting and data management tasks.

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