Aggregating Year-to-Date Data in Microsoft Dynamics 365

Views (3)

Travis South

Like(0)

Report

Microsoft Dynamics 365 provides a wide range of convenient tools for IT teams to deliver efficient and accurate reporting of business partner data. A common request, particularly in sales, involves displaying year-to-date values for various fields (revenue, costs, units sold). It seems simple enough. However, complications can arise when aggregating data across multiple entities in Microsoft Dynamics 365.

Aggregating Data for Year-to-Date Revenue by Account

A company has multiple Accounts, and each Account has Orders that are associated with it. The requirement is to display the total Year-to-Date Revenue on each Account. Each Order includes an Order Total, which represents the currency amount received. It also includes an Order Date, which indicates when the Order was placed. This new Year-to-Date Revenue field sums the Order Total values on Orders associated with an Account. Only Orders placed in the current calendar year are included.

Common Approaches to Aggregating Data and Their Limitations

We only need to build logic for one field. Ideally, this should not require complex development. However, as we investigate further, we find that it is not that simple.

Calculated/Formula Columns: Simple, but Limited
Calculated columns are the easiest way to automatically compute data that relies on other fields. The column itself does the calculation and does not require any additional workflows/code. Another benefit is that the calculation is synchronous. As a result, the value stays up to date automatically. However, Calculated Columns cannot access data from multiple entities. Since we want the field on the Account to display the sum of a field from another table, this approach does not meet the requirement.

Rollup Columns: Powerful, but Not Year-Aware
These are very similar to Calculated Columns. The main difference is that Rollup Columns can connect to other entities. This typically is the most efficient way to gather data for reporting. One drawback is that these fields are not synchronous. They update automatically on a 12-hour schedule, or they can be recalculated manually. This approach works only when real-time data is not required. Regardless, in this scenario, we are looking for Year-to-Date functionality. Rollup Column date field conditions can only filter on fixed date ranges and do not support a ‘This Year’ operator. Therefore, this also will not fulfill our need.

Design Considerations When Aggregating Data Across Entities

We have ruled out simple column computations, so we will need to look at workflows and code. All the options below can work for this task. However, you should consider the pros and cons of each approach when choosing the best solution.

Workflow and Code-Based Approaches for Aggregating Data

Power Automate: Flexible, but Costly at Scale
Power Automate is an easy and user-friendly way to manipulate data across multiple entities and systems. Power Automate can handle this scenario. However, there are several limitations to consider. Orders can be created and updated at any time. As a result, the workflow would need to trigger on each event. Depending on system traffic, this workflow would run frequently. It would also recalculate the same data. Over time, this becomes inefficient. If real-time updates are not a concern, the flow can run on a schedule. Even then, unnecessary recalculations still occur. Furthermore, if we are dealing with a large number of Accounts and Orders, Power Automate can execute very slowly.

Plugins (C# Code): Scalable, but Developer-Dependent
Plugin code runs similarly to Power Automate. However, plugins scale much better for high-volume transactions. This approach optimizes performance for our scenario. The downside to choosing this route is it requires more advanced developer knowledge to setup and maintain, as you will need to understand C# and/or similar coding syntax to build out this logic.

JavaScript: Fast for Forms, Limited Beyond Them
If we want to avoid running a workflow or plugin every time an Order is created or updated, we can use JavaScript to obtain the required data. This will run any time the Account form is loaded and is a great way to handle computing a field that requires filters and relies on other entities. JavaScript will calculate the data to display on the form, but it will not save the value to the given table. Meaning, if that same data is needed in other areas (dashboards or forms), this is not an efficient way to grab data for long term use.

A More Efficient Way of Aggregating Data with FetchXML

Once you have chosen which option makes the most sense for your needs, it is now time to build out the logic. There are many ways to do this, but collecting and summing mass data can lead to unnecessary looping. The most streamlined way is to aggregate data through a FetchXML Query. FetchXML includes grouping and aggregation features that let you calculate sum, average, min, max, and count across multiple rows of data.

Let us look at the following example:

How FetchXML Reduces Looping and Improves Performance

This query uses the aggregate ‘sum’ function on the Order Total field, while filtering for those Orders that belong to the provided Account and fall within the date range we want. This single call provides everything we need. No looping is required. This approach lets the platform handle aggregation at the query level. It avoids iterating through individual records in workflows or custom code. As a result, it avoids iterating through individual records in workflows or custom code. From here, you can add additional conditions to meet the requirements. Some other suggestions would be to only look at active records or filter out Orders that are cancelled/returned.

Additional Supporting Microsoft Documentation

Aggregate Data using FetchXml - Power Apps | Microsoft Learn

Key Takeaway: Optimize for Sustainability, Not Just Possibility

Aggregating data in Microsoft Dynamics 365, especially for year-to-date reporting across related entities, rarely comes down to what is possible, as we learned above there are many ways to perform these tasks. However, it is more so about what is efficient and sustainable for long term use. FetchXML calls via workflows or code offer a way to reduce system load and improve reliability when traditional column functions cannot handle the task.

How To Series Aggregating Year-To-Date Data in Microsoft Dynamics 365

Working with New Dynamic

New Dynamic is a Microsoft Solutions Partner focused on the Dynamics 365 Customer Engagement and Power Platforms. Our team of dedicated professionals strives to provide first-class experiences incorporating integrity, teamwork, and a relentless commitment to our client’s success.

Comments

*This post is locked for comments

Community site session details