Power Automate the creation of Rollup Queries and Goals for Product Sales

In a previous blog post I wrote about Goals for Product Sales. It would be nice to Power Automate the creation of the Rollup Queries and Goals for selected Products, so I’ve created Flows to do this job.

First Flow is to create a Rollup Query based on the selected Product. Here it is:

I’ve used Advanced Find to create the query and download the FetchXML that can be pasted in the Condition Criteria field, and added the Product Name and Product (gu)id as dynamic content:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="opportunityproduct">
    <attribute name="productid" />
    <attribute name="productdescription" />
    <attribute name="priceperunit" />
    <attribute name="quantity" />
    <attribute name="extendedamount" />
    <attribute name="opportunityproductid" />
    <order attribute="productid" descending="false" />
    <filter type="and">
      <condition attribute="productid" operator="eq" uiname="@{triggerBody()?['entity']?['name']}" uitype="product" value="{@{triggerBody()?['entity']?['productid']}}" />
    </filter>
  </entity>
</fetch>

This will create a Rollup Query for the selected Product, like for example:

Please be aware of the fact that Microsoft renamed the Opportunity Product entity to Opportunity Line, so the schema name and display name can differ.

The second Flow is set up to create Goals for Product Sales, based on a selected Rollup Query.

Input fields for Target and Fiscal Year can be added to the trigger.

The guid of the to be used Metric is added to the variable Metric Id.
Then I create an Array with the values of the months from the Fiscal Period global option set:

For each Month a new Goal record is created. The Name of the Goal is based on the name of the Product that I get from the FetchXML of the Rollup Query record with the xpath() function in the expression:

xpath(xml(body('Get_Rollup_Query_record')?['fetchxml']),'string(/fetch/entity/filter/condition/@uiname)')

in combination with the number of the Fiscal Year and Fiscal Period, the latter with the expression:

substring(string(items('Apply_to_each')),1,2)

The Goal Owner and Manager should be set to the dedicated user.

In this way it has become easy to create Goals for Product Sales.

Update 2019-12-17:
Recalculate Goal action is now available in the Common Data Service (current environment) connector as a bound action.