Scenario Overview

In this scenario we have an Advertising company selling TV ads for a period of time with the need to calculate monthly pro-rata dates.

These TV ads were represented as Opportunities in the Dynamics 365 system where you had a campaign start & end date.

Rules for Setting Monthly Dates

Rule # Description

Delete all pre-existing Opportunity ProRata records associated with the opportunity.


Calculate the number of months between the campaign start and end dates.


Create a new Opportunity ProRata record for every month involved, setting the start and end dates. 

North52 Decision Suite Solution


Formula Header

The North52 Decision Suite solution works like this:

  • A Formula is created which executes whenever an Opportunity is created or updated.
  • It checks to see if either of the following fields are changing before it will continue its execution, Campaign Start Date and Campaign End Date

Formula Body

This part of the North52 Decision Suite Solution describes the individual steps that make up the formula. When dealing with these complex formulas it is usually best to think of them like a CRM workflow where each line in the SmartFlow statement is like a step in a CRM workflow.

  • Find any existing records & delete them. Executes a fetch-xml statement to find the records.
  • Calculates the overall Number of Months involved
  • Loops over the number of months where it will create 1 Opportunity ProRata record per month


  • Add 2 new datetime fields to the Opportunity entity called Campaign Start Date and Campaign End Date
  • Create a new entity called Opportunity ProRata using the screenshot below as a reference

North52 Decision Suite Steps

  • Create a new Formula of type Save - Perform Action
  • Set the Event to Create & Update
  • Set the Source Entity to Opportunity
  • Set the Source Property to Campaign Start Date and Campaign End Date
  • Copy & paste the formula below into the formula description field & click save
  • Next scroll down to the formula detail grid & add a new record
  • Give it the name RemoveOldRecords
  • Copy & paste the fetch-xml below into the query field & click save
  • You are now ready to test





     SetVar('NoOfMonths', DateDiff([opportunity.new_campaignstartdate], [opportunity.new_campaignenddate], 'pm') ), 
     DoLoop( GetVar('NoOfMonths') , 
                              SetAttribute('new_name', [] + ' - ' +  DoLoopIndex()),
                              SetAttributeLookup('new_opportunityid', 'opportunity', [opportunity.opportunityid]),
                                           if( DoLoopIndex() = 0 ,
                                               GetFirstDayOfMonth( [opportunity.new_campaignstartdate], DoLoopIndex()) ) ),
                                             if( DoLoopIndex() = GetVar('NoOfMonths')-1,
                                                  GetLastDayOfMonth( [opportunity.new_campaignstartdate], DoLoopIndex())))           


<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="new_opportunityprorata">
    <attribute name="new_opportunityprorataid" />
    <attribute name="new_name" />
    <filter type="and">
      <condition attribute="new_opportunityid" operator="eq" value="@opportunityid@" />

Wizard - GetFirstDayOfMonth

Please see below the wizard you can use to create the GetFirstDayOfMonth() function call used in this formula. 


Did you know?

xCache helps you manage environment specific configuration

North52's Decision Suite component xCache allows you to set up environment specific data to allow you to use different values for different instances. Your advanced business rules logic could be the same, however you need to use specific reference values for a particular instance.

For example, credentials for a web service used in your Formula - you will likely use different keys/URLs for your Dev, Test and Production instances. Store these in xCache and let the North52 business rules engine apply the correct ones at runtime! 

Learn more and see examples of xCache