KB37227: How to create a custom subtotal which does the difference between or sum of the last two metric values in MicroStrategy

A custom subtotal to create the difference or sum between the last two metric values across an attribute can be created in Strategy using OLAP functions in a custom subtotal. This solution avoids the need for additional underlying metrics which could incur computational or performance costs. The functions that can be used are the movingdifference or movingsum functions.
 
The example below outlines how to create a Sum custom subtotal that only totals the last two metric values
 
Example:

1. Create a new custom subtotal object.
2. Create a formula in it of.
   
   Max(MovingSum(Sum(x){~},2)){@}
   
   Now the Sum(x) {~} is there because custom subtotals don't support having OLAP functions operate directly on input metrics (MovingSum(x,2) is invalid). The Max wrapper around the formula is again so the outermost function is a full aggregation function.
3. Highlight and right click on movingsum then select the parameters option.
4. Set the break by parameter to whatever attributes should be broken by on the report. For example if the sum of the last two year values for a metric per each region is the desired subtotal then place region into the break by.
5. Set the sort by parameter to whatever attribute that the sum of the last two elements is supposed to be of. Per the above statement the sort by would be year ID for this custom subtotal.
6. Edit the metric(s) which want to use this custom subtotal and add it as a valid subtotal for it in the subtotals/aggregation tab. Finally either make it the subtotal function for the metric or use advanced subtotals on the reports to turn on those sum subtotals.

 
For the scenario with a sort by quarter it returns the following values on a report.
 

 
Each subtotal returns the sum of the last two Quarters values regardless of the level of the subtotal as desired.
 
Different custom subtotals will have to be created for different levels or break bys. However the same subtotals can be used for multiple metrics