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 are represented as Opportunities in the Dynamics 365 system where you had a campaign start and end date along with a budget amount. 

We need to calculate on a pro-rata basis the amount for each month given the number of days in the month that the campaign would run for. Each month will be added as an Opportunity Product record. 

Rules for Calculating Monthly Amounts

Rule #	Description
1	Notify the user if the Campaign Start Date, Campaign End Date or Budget Amount fields do not contain data.
2	Delete pre-existing Opportunity Products.
3	Calculate the amount of revenue per day for the overall time period.
4	Create one Opportunity Product record for each month.

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 Date, Campaign End Date and Budget Amount
• Using the SmartFlow capability and multiple functions we can break the formula down into a series of steps similar to a standard CRM workflow:
  • Step 1: Ensure that the Campaign Start Date, Campaign End Date and Budget Amount fields contain data otherwise display an error
  • Step 2: Find any existing records and delete them. Executes a fetch-xml statement to find the records.
  • Step 3: Calculates the overall Number of Months involved
  • Step 4: Calculates the overall Number of Days involved
  • Step 5: Calculates the Amount of revenue per day
  • Step 6: Loops over the number of months where it will create one Opportunity Product record per month
• The calculations for the Amount and No Days 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 on within the DoLoop: 
  • Instance 1: First month of the DoLoop and there is just 1 month involved 
  • Instance 2: First month of the DoLoop and there are more than 1 month involved
  • Instance 3: Last month of the DoLoop 
  • Instance 4: Neither the First or Last Month of the DoLoop

Setup 

• Add two new Time-Zone Independent datetime fields to the Opportunity entity called Campaign Start Date and Campaign End Date
• Create a Price List if you do not already have one (it does not need Price List Items for this example)

North52 Decision Suite Steps

Set up the Formula:

• Create a new formula, setting the following values in the Formula Guide
  • Source Entity set to Opportunity
  • Set Formula Type to Save - Perform Action
  • Select the Classic editor
  • Click the Create button
• Change the Name of the formula to Opportunity - Calculate Pro Rata Opportunity Products
• Expand the Source & Target section and Set the Source Property to:
  • CTRL-click to multi-select Campaign Start Date, Campaign End Date, Budget Amount
• Click Save

Step 1: Ensure that the Campaign Start Date, Campaign End Date and Budget Amount fields contain data otherwise display an error

• Click inside the Formula editor canvas
• From the Snippets menu select If
• Delete the expression placeholder text
• Select the Functions tab and type contains into the Search Functions input box and press enter
  • Shift-click the ContainsData function to show the wizard
  • Search and select Campaign Start Date, Campaign End Date, Budget Amount
  • Click Generate
• Delete the false placeholder text and press enter twice, and move the cursor up one line
• Select the Functions tab and type Throw into the Search Functions input box and press enter
  • Shift-click the ThrowError function to show the wizard
  • In the Enter string for error message field type Campaign Start Date, Campaign End Date, and Budget Amount must contain values
  • Click Generate
• Click Save

Step 2: Find existing Opportunity Product records and delete them

• Select the Fetch-Xml tab
• Click the + button to create a new Formula Detail record
• Set the Name to Delete Existing Opportunity Product Records
• Open the Advanced Find
  • Set the Look for to Opportunity Products
  • Select Opportunity equals and lookup any Opportunity (it doesn't matter which as we will replace it with a dynamic placeholder later)
  • Click the Edit Columns button and set it so that only the Opportunity column is shown
    • Click Ok
  • Click the Download Fetch XML button
  • Open with Notepad (not a browser like Internet Explorer) and copy the Fetch-Xml
• Paste the Fetch-Xml into the Query field on the form
  • For readability remove the uiname and uitype parameters from the Fetch-Xml
  • Replace the GUID in the value parameter with @opportunityid@ - this will be replaced by the Opportunity record GUID at runtime, your Fetch-Xml should look like this:

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="opportunityproduct">
        <attribute name="opportunityproductid" />
        <attribute name="opportunityid" />
        <order attribute="opportunityid" descending="false" />
        <filter type="and">
          <condition attribute="opportunityid" operator="eq" value="@opportunityid@" />
        </filter>
      </entity>
    </fetch>

• Click Save
• Close the Formula Detail form to return back to the formula
• Delete the true placeholder text and press enter twice, and move the cursor up one line
• Select the Functions tab and type smartflow into the Search Functions input box and press enter
• Click the SmartFlow function to add it to the formula canvas
• Delete the placeholder parameters and press enter twice, and move the cursor up one line
• Select the Functions tab and type delete into the Search Functions input box and press enter
• Click the DeleteRecordsFD function to add it to the formula canvas
• Delete the placeholder text 'formuladetailname'
• Select the Fetch-Xml tab and expand the Query Details node
• Click the Delete Existing Opportunity Product Records node to add the query name to the DeleteRecordsFD function
• To tidy up the formatting of a formula we can select all of the formula text (press CTRL-a) and then press Shift-Tab, this will auto indent the formula correctly. Your formula should now look like this:
  
• Click Save
• Test the formula in its current form
  • Firstly click the tick icon on the formula canvas top bar to test the structural integrity
    
  • Then create an opporutnity record and test that the formula deletes existing opportunity product records or displays the error message

Step 3: Calculate the overall Number of Months involved

• Immediately after the closing bracket of the DeleteRecordsFD function type a comma, and press enter twice
• Select the Functions tab and type setvar into the Search Functions input box and press enter
• Click the SetVar function to add it to the formula canvas
• Replace the variablename placeholder text with NoOfMonths
• Delete the placeholder text 'value'
• Select the Functions tab and type datediff into the Search Functions input box and press enter
• Shift-click the DateDiff function to show the wizard
  • From Date: Select Campaign Start Date from the search list
  • To Date: Select Campaign End Date from the search list
  • Interval: Select Months
  • Click Generate to add the function to the formula
    
• Temporary Step until function wizard supports Period Months: Replace the DateDiff 'm' parameter with 'pm' - this stands for Period Months and returns all the months in the specified period ignoring days e.g. 3 Mar 2018 to 1 June 2018 will return 4 for March, April, May and June even though there are not 4 full months. 

Step 4: Calculate the overall Number of Days involved

• Immediately after the closing bracket of the NoOfMonths SetVar function type a comma, and press enter
• You could use the function wizard as per the previous step however it is very similar so copy the SetVar function for the NoOfMonths and paste it on the new line you have just created
• Replace the NoOfMonths text with NoOfDays
• Change the 3rd DateDiff parameter 'm' to 'd'
• Add +1 after the DateDiff function and inside the closing bracket of the SetVar function

Step 5: Calculate the Amount of revenue per day

• Immediately after the closing bracket of the NoOfDays SetVar function type a comma (if you didn't copy it in the step above), and press enter
• Select the Functions tab and type setvar into the Search Functions input box and press enter
• Click the SetVar function to add it to the formula canvas
• Replace the variablename placeholder text with SingleDayAmount
• Delete the placeholder text 'value'
• Select the Source tab and type budget into the Search source input box and press enter
• Expand the Source node and click on Budget Amount to add the variable placeholder [opportunity.budgetamount] to the formula
• Immediately after [opportunity.budgetamount] type a foward slash / followed by GetVar('NoOfDays')
• Click Save
• Your formula should now look like this:
  

Step 6: Loop over the number of months and create one Opportunity Product record per month

• Immediately after the closing bracket of the SingleDayAmount SetVar function type a comma, and press enter twice
• Select the Functions tab and type doloop into the Search Functions input box and press enter
• Click the DoLoop function to add it to the formula canvas
• Replace the 'iterationcount' placeholder text with GetVar('NoOfMonths') - we want to create an Opportunity Product per month
• Delete the placeholder text 'action' and press enter twice, then move the cursor up one line
• Select the Explore tab and type opportunity into the Search data model input box and press enter
• Expand the Opportunity Product node, and check:
  • Opportunity (Value)
  • Price Per Unit
  • Quantity
  • Write-In Product
• From the Select Generator drop down choose Create Record then click the Lightning Bolt icon. A CreateRecord function with the selected fields and placeholder attributes is added to the formula
  • SetAttributeLookup or SetAttribute (depending on your version of BPA) - 'opportunityid'
    • Replace entitylogicalname with opportunity (SetAttributeLookup only)
    • Delete 'value'
    • Select the Source tab and type opportunity into the Search source input box and press enter
    • Expand the Source node and click on Opportunity to add the variable placeholder [opportunity.opportunityid] to the function
  • SetAttribute - 'priceperunit'
    • Replace value with GetVar('SingleDayAmount')
  • SetAttribute - 'quantity'
    • Replace value with 1 - this is temporary, we will come back an replace this with a case function later
  • SetAttribute - 'productdescription'
    • Replace 'value' with

    • ToString(AddMonths([opportunity.new_campaignstartdate], DoLoopIndex()),'MMMM yyyy') 

      •  this will add the Month and Year to the Opportunity Product line items
• To tidy up the formatting of a formula we can select all of the formula text (press CTRL-a) and then press Shift-Tab. Your formula should now look like this:
  
• Click Save
• Test the formula by creating Opportunities with different Budget Amount and Campaign Start and End Dates, you should get Opportunity Product records created

We now need to calculate the number of days for each month and set this in the Quantity field of the Opportunity Product records. We use a Case function for the 4 instances we identified above in the overview of the formula solution:

• Delete the '1' from the SetAttribute - 'quantity' function and press enter 3 times, then move the cursor up twice
• Select Case from the Snippets menu at the top of the formula editing canvas
• Delete the [account.name] example parameter and type true in its place

Instance 1: First month of the DoLoop and there is just 1 month involved 

• Above the first When function type the comment  /* First month of the DoLoop and there is just 1 month involved */ 
• Delete the example text 'Microsoft' from the When function
• Select the Functions tab and type doloop into the Search Functions input box and press enter
• Click the DoLoopIndex function to add it to the formula canvas, then type = 0 and  immediately after
• From the Functions list click the DoLoopTotal function, then type = 1  immediately after
• Replace the example text 'Fantastic' from the Then function with GetVar('NoOfDays')

Instance 2: First month of the DoLoop and there are more than 1 month involved

• Above the second When function type the comment  /* First month of the DoLoop and there is more than 1 month involved */ 
• Delete the example text 'North52' from the When function
• Select the Functions tab and type doloop into the Search Functions input box and press enter
• Click the DoLoopIndex function to add it to the formula canvas, then type = 0 and  immediately after
• From the Functions list click the DoLoopTotal function, then type > 1  immediately after
• Delete the example text 'Great'
• Select the Functions tab and type datediff into the Search Functions input box and press enter
• Shift-click the DateDiff function to show the wizard
  • From Date: Select Campaign Start Date from the search list
  • To Date: Select Campaign Start Date again from the search list
  • Interval: Select Days
  • Click Generate to add the function to the formula
• Highlight the second parameter of the DateDiff function [opportunity.new_campaignstartdate]
• Select the Functions tab and type getlast into the Search Functions input box and press enter
• Click on the GetLastDayOfMonth function - it will incorporate the highlighted text as its first parameter
• Delete the second parameter placeholder text 'addmonths' and the preceeding comma as it is not needed for this function
• Add +1 after the DateDiff function and inside the Then function - we do this as want to include the last day in the calculation, not just the difference between the dates

Instance 3: Last month of the DoLoop 

• After the closing bracket of the Then function and the following comma from the step above and press enter
• Type the comment  /* Last month of the DoLoop  */ and press enter
• Type When() to add a new When clause. Place the cursor inside the When() brackets
• Select the Functions tab and type doloop into the Search Functions input box and press enter
• Click the DoLoopIndex function to add it to the formula canvas, then type = GetVar('NoOfMonths')-1 immediately after
• Following the When function type Type , Then(), (including the preceding and trailing commas) to add a new Then clause. Place the cursor inside the Then() brackets
• Select the Functions tab and type datediff into the Search Functions input box and press enter
• Shift-click the DateDiff function to show the wizard
  • From Date: Select Campaign Start Date from the search list
  • To Date: Select Campaign End Date from the search list
  • Interval: Select Days
  • Click Generate to add the function to the formula
• Highlight the first parameter of the DateDiff function [opportunity.new_campaignstartdate]
• Select the Functions tab and type getfirst into the Search Functions input box and press enter
• Click on the GetFirstDayOfMonth function - it will incorporate the highlighted text as its first parameter
• Delete the second parameter placeholder text 'addmonths' 
• Select the Functions tab and type doloop into the Search Functions input box and press enter
• Click the DoLoopIndex function to add it to the formula canvas
• Add +1 after the DateDiff function and inside the Then function - we do this as want to include the last day in the calculation, not just the difference between the dates

Instance 4: Neither the First or Last Month of the DoLoop

• Above the Default parameter of the Case function type the comment /* Neither the First or Last Month of the DoLoop */
• Delete the example text 'Not Microsoft or North52' 
• Select the Functions tab and type getdays into the Search Functions input box and press enter
• Shift-click the GetDaysInMonth function to show the wizard
  • Input Date: Select Campaign Start Date from the search list
  • Click Generate to add the function to the formula
• Add a second parameter to the GetDaysInMonth function by typing a comma after the [opportunity.new_campaignstartdate] dynamic field reference text
• Select the Functions tab and type doloop into the Search Functions input box and press enter
• Click the DoLoopIndex function to add it to the formula canvas
• To tidy up the formatting of a formula we can select all of the formula text (press CTRL-a) and then press Shift-Tab. Click Save. Your formula should now look like this:
  

Testing

Create an Opportunity and try setting several different values for the Campaign Start Date, Campaign End Date and Budget Amount. The Opportunity Products subgrid will update with the pro rata Write-In products for each month:

Formula

The full final formula:

if(ContainsData([opportunity.new_campaignenddate],[opportunity.new_campaignstartdate],[opportunity.budgetamount]), 

  SmartFlow(
    DeleteRecordsFD('Delete Existing Opportunity Product Records'),

    SetVar('NoOfMonths', DateDiff([opportunity.new_campaignstartdate],[opportunity.new_campaignenddate],'pm')),
    SetVar('NoOfDays', DateDiff([opportunity.new_campaignstartdate],[opportunity.new_campaignenddate],'d')+1),
    SetVar('SingleDayAmount', [opportunity.budgetamount]/GetVar('NoOfDays')),

    DoLoop(GetVar('NoOfMonths'), 
      CreateRecord('opportunityproduct', 
        SetAttributeLookup('opportunityid', 'opportunity' , [opportunity.opportunityid]),
        SetAttribute('priceperunit', GetVar('SingleDayAmount')),
        SetAttribute('quantity', 

          Case(true, 
            /* First month of the DoLoop and there is just 1 month involved */ 
            When(DoLoopIndex() = 0 and DoLoopTotal() = 1), Then (GetVar('NoOfDays')),
            /* First month of the DoLoop and there is more than 1 month invloved */ 
            When(DoLoopIndex() = 0 and DoLoopTotal() > 1), Then (DateDiff([opportunity.new_campaignstartdate],GetLastDayOfMonth([opportunity.new_campaignstartdate]),'d')+1),
            /* Last month of the DoLoop  */
            When(DoLoopIndex() = GetVar('NoOfMonths')-1), Then(DateDiff(GetFirstDayOfMonth([opportunity.new_campaignstartdate], DoLoopIndex()),[opportunity.new_campaignenddate],'d')+1),
            /* Neither the First or Last Month of the DoLoop */
            Default(GetDaysInMonth([opportunity.new_campaignstartdate], DoLoopIndex()))
          )

        ),
        SetAttribute('productdescription', ToString(AddMonths([opportunity.new_campaignstartdate], DoLoopIndex()),'MMMM yyyy'))
      )
    )

  ), 

  ThrowError('Campaign Start Date, Campaign End Date, and budget Amount must contain values')
)