Microsoft provides ability to create rollup fields out of the box. The problem with rollup field is, you are not allowed to use calculated field as a filter in rollup field.
In this blog post, I will explain how we can use custom filtering and custom calculation using flow
Functional requirement: Calculate rollup of 'Actual Revenue' field from opportunities that were closed (actual closed date) in last year. Save the rollup value on the related account of an opportunity.
Technical Steps:
1. Create a currency field in Account, name it as 'Last Year Revenue'.
a. Goto make.powerapps.com
b. Ensure that you are in correct environment.
c. Goto Data->Entities and select Account entity.
d. Create new field of type Currency (do-not add Rollup or calculation)
2. Create a flow to capture details from opportunity and store it into above field for related account:
a. On make.powerapps.com, click on flow
b. Click on New->"Scheduled from blank"
c. Enter the flow name or else it will be created automatically.
d. Manage the flow run date and time. Change repeat every from 1 minute to 1 day (as required):
e. Upon creation of flow add a new step, "Get Past Time":
f. Set Interval to 12, and time unit to "month" (as per your requirement).
g. Add a step to Initialize a variable of float data type with the value of 0:
h. Add a step to list all records. Select targeted environment and select Accounts in entity.
i. Add a new step "Update a record", select account as entity and for unique identifier use Account identifier from the above step. As soon as you add unique identifier it should automatically add "Apply to each":
j. Click on show advanced options, and update the field "Last Year Revenue" to 0:
k. Now its time to get all opportunities that are closed in last year, linked to each account. Add a new step to list all records. Select Opportunities as entity and add filter query. The filter query would be: _parentaccountid_value eq @{items('For_each_Account')?['accountid']} and actualclosedate ge @{body('Get_past_time')}
l. Add a new step "Increment Variable", select SumRevenue as the Variable and for value select "Actual Revenue" (From above step). As soon as you add Actual Revenue, flow will automatically add Apply to each step before this step:
m. Finally step is to update the account with the SumRevenue Value. Please note, we are updating account inside of "For each Opportunity":
Finally Here is an overview of the flow:
For testing purpose, I have created 3 opportunities under 2 separate accounts:
Let's test the flow:
Limitations:
1. "Apply for each" step has limitations on the number of records based on your license. If you have flow for free, limit is 5,000 records. If you have Office 365, Plan 1, Plan 2, Per User, and Per Flow licenses, limit is 100,00. Be careful with the number of accounts you have, and the license for flow you have.
I hope this helps. Please let me know if you have any questions/concerns.
Thanks
Jay