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 amounts . These TV ads were represented as Opportunities in the CRM system where you had a campaign start & end date along with an estimated revenue. 

We need to calculate on a pro-rata basis the amount of revenue for each month given the number of days in the month that the campaign would run for & store this data into 1:N related entity.


North52 Decision Suite Solution

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 & End Dates and Estimated Revenue

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.

  • Step1: Find any existing records & delete them. Executes a fetch-xml statement to find the records.
  • Step2: Calculates the overall Number of Months involved
  • Step3: Calculates the overall Number of Days involved
  • Step4: Calculates the Amount of revenue per day
  • Step5: Loops over the number of months where it will create 1 Opportunity ProRata record per month

The calculations for the Amount & NoDays fields are taking into account 4 key instances via the case statement. This case statement needs to calculate the correct values based on what month we are working with the DoLoop.  

  • Instance1: First month of the DoLoop & there is just 1 month involved 
  • Instance2: First month of the DoLoop & there are more than 1 month invloved
  • Instance3: Last month of the DoLoop 
  • Instance4: Neither the First or Last Month of the DoLoop


  • Add 2 new datetime fields to the Opportunity entity called Campaign Start Date, 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, Campaign End Date& Estimated Revenue'
  • 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') ), 
     SetVar('NoOfDays', DateDiff([opportunity.new_campaignstartdate], 
                                [opportunity.new_campaignenddate], 'd') +1 ), 
     SetVar('SingleDayAmount',  [opportunity.estimatedvalue] / GetVar('NoOfDays') ), 

     DoLoop( GetVar('NoOfMonths') , 
                             [] + ' - ' +
                           'opportunity', [opportunity.opportunityid]),
                           SetAttribute('new_startdate', if( DoLoopIndex() = 0 ,
                                          GetFirstDayOfMonth( [opportunity.new_campaignstartdate],
                                                               DoLoopIndex()) ) ),
                          SetAttribute('new_enddate', if( DoLoopIndex() = GetVar('NoOfMonths')-1,
                                               GetLastDayOfMonth( [opportunity.new_campaignstartdate],
                                        Case( true,
                                           When( DoLoopIndex() = 0 and DoLoopTotal() = 1), 
                                           Then ( [opportunity.estimatedvalue] ),
                                          When( DoLoopIndex() = 0 and DoLoopTotal() > 1), 
                                          Then ( (DateDiff([opportunity.new_campaignstartdate] ,
                                             GetLastDayOfMonth( [opportunity.new_campaignstartdate],
                                             DoLoopIndex()), 'd')+1)   * GetVar('SingleDayAmount') ),
                                                When(DoLoopIndex() = GetVar('NoOfMonths')-1), 
                                                'd')+1)* GetVar('SingleDayAmount')),
                                                 Default( GetDaysInMonth(
                                                       DoLoopIndex())* GetVar('SingleDayAmount')) 
                                                                   Case( true,
 	                                                              When( DoLoopIndex() = 0 and 
                                                                   DoLoopTotal() = 1), 
                                                                 Then ( GetVar('NoOfDays') ),
                                                                When( DoLoopIndex() = 0 and
                                                                DoLoopTotal() > 1), 
                                                               Then ( DateDiff(ToString(
                                                               [opportunity.new_campaignstartdate]) , 
                                                               'd')+1  ),

                                            When(DoLoopIndex() = GetVar('NoOfMonths')-1), 
                                           Then ( DateDiff( GetFirstDayOfMonth(
                                                 [opportunity.new_campaignenddate], 'd')+1  ),                          
                                                           Default( GetDaysInMonth(
                                                            DoLoopIndex()) ) 
      SmartFlowReturn(  'NoOp'  )


<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. 

Note for Offset, you will need to type in "Offset"

Did you know?

You can easily schedule recurring processes or CRM record updates

Every business has recurring processes and tasks that need to be done on repeatable and scheduled basis. Microsoft Dynamics 365 users are often surprised to learn that it is difficult to achieve using standard functionality. The North52 Scheduler makes it easy to trigger workflows and/or advanced business logic for specific entity records on a scheduled basis. 

Key features include:    

  • Create Recurring Schedules within Dynamics CRM
  • Powerful Record Targeting - easily create queries to target a specific set of records
  • High Performance - can process 100,000+ records on a scheduled basis
  • Execute Workflow/Action or Update Entities
  • Native to Dynamics 365

Learn more about the Scheduler