You're correct that Dynamics 365 rollup fields don’t support dynamic filters like “Year-to-Date” (YTD) directly, as rollups only allow static criteria. However, there are some effective workarounds to achieve this:
1. Using Power Automate for Scheduled Updates
You can set up a Power Automate flow to calculate YTD sales and update a custom field on the Account record.
Create a flow that runs on a schedule (e.g., daily or weekly) to:
Retrieve all fulfilled orders related to the account.
Filter the orders by the current year.
Sum the amount fields from these orders.
Update the “YTD Sales” field on the Account with the calculated total.
Advantages:
Dynamic filtering based on dates is possible.
Reduces the need for complex plugin code.
2. Custom Plugin for Real-Time Updates
If real-time updates are important, a custom plugin can calculate the YTD sales each time an order is created, updated, or fulfilled.
The plugin can fetch the orders for the account, filter them based on the current year, sum the amount, and update the YTD field.
Advantages:
Immediate updates for YTD values.
Flexibility in calculations based on current date and status.
3. Alternative: JavaScript on Form (for Display-Only)
You can also use JavaScript on the Account form to calculate and display YTD sales dynamically when the form is loaded. And update it using web api.
Only limitation is that it won't work during import operation.
