KB9666: How to create a custom subtotal using the “Last” function to display the Subtotal for an 'End on hand' metric in MicroStrategy Desktop

This document uses the Strategy Tutorial project to demonstrate how to create a custom subtotal using the "Last" function to display the subtotal of an 'End on hand' metric. For this example, an 'End on hand' metric must be created based on the 'Item inventory' fact. The definition of this metric will be as follows:
Sum() {~}
Consider the following scenario where a user wants to display the last month of inventory for the year as the subtotal for a report. This report will include attributes 'Year' and 'Month' and the 'End on hand' Inventory metric. The following procedure describes how to create such a custom subtotal for this purpose:
 

• Open the Subtotal editor and create a custom subtotal by using the "Last" function, as shown below:
  

•  
  Last(x){@}
  
• Right-click on the highlighted "Last" function and select the function's parameters.
• Set the Sort By parameter for the function, as shown below:
  

• Save this custom subtotal in the project's "Project Objects/Subtotals" directory.
• Open the "Inventory" metric on which the subtotal will be applied.
• In the "Subtotals" tab, add this subtotal into "Available subtotals for metric", as shown in following screen shot:
  

• In the report, enable the subtotal in the Data > Subtotal menu:
  

Once this is completed, users will receive the following report with the last value for the elements in the subtotals: