KB13777: How to create a Last(x) custom subtotal and apply the grand total in the same report for MicroStrategy Developer

The user has created a report with with the attributes Region and Quarter and the metric Revenue. The users wants to have a subtotal for each region showing the revenue for the last quarter (Q4). At the bottom of the report, the user wants to display a grand total showing the sum of the revenue values for Q4 in each region, as shown below:
 

 
Follow the steps below to create the Sum of Last and Last Quarter subtotals in the Strategy Tutorial project:
 

• Create a custom subtotal with the formula 'Last(x) {@}' as shown below:
  

• Save this subtotal as 'Last(x).'
• Right-click on the function Last to open the Last parameters. In the Sort by tab, add the Quarter ID and the Region ID to sort their values in ascending order, as shown below:
  

• Create a new metric with the definition 'Last(Sum(Revenue) {~} ) {~, Quarter}.' This metric will pick up the last quarter revenue for each of the regions in the report. In the 'Level' for the metric, ensure that the grouping for the Quarter level is set to 'None', as shown below:
  

• Save this metric as 'Inter Metric.'
• Next create a second custom subtotal with the formula 'Sum() {@}' where is the name of the previously created metric, as shown below:
  

• Save this second custom subtotal as 'Sum of Last Quarter.'
• Add the above two custom subtotals to the 'Revenue Metric,' as shown below:
  

• Create a report with Region and Quarter in the rows and the metrics Revenue and Inter Metric in the columns. For this example, the filters Year = 2004 and Region = Northeast, Mid-Atlantic, Southeast, Central have been added to restrict the data being returned, as shown below:
  

• Open the Data menu -> Subtotals and click on Advanced. Create a new custom subtotal defined as follows:
  

• Call the new custom subtotal 'Last Quarter.'
• Apply the new custom subtotal 'Last Quarter' across level for the Quarter attribute, as shown below:
  

• Apply the custom subtotal 'Sum of Last' 'By position' in rows and pages as Grand Total, as shown below;
  

• Execute the report. If it is not needed to display the 'Inter Metric,' set the column width for this attribute to 0, as shown below: