Calculate Period-to-Date Values in an Accounts Dimension

If the Essbase block storage outline includes a dimension tagged as accounts, you can use the @PTD calculation function to calculate relevant period-to-date values. Or, you can use Dynamic Time Series members.

This example uses the @PTD function in member formulas added to a time dimension.

Consider the Inventory branch of the Measures dimension from the Sample Basic database:

Inventory (~) (Label Only)
   Opening Inventory (+) (TB First) (Expense Reporting) 
      IF(NOT @ISMBR(Jan))
   Additions (~) (Expense Reporting)
   Ending Inventory (~) (TB Last) (Expense Reporting)

To calculate period-to-date values for the year and for the current quarter, add two members to the Year dimension: QTD for quarter-to-date and YTD for year-to-date. For example:

QTD (~) @PTD(Apr:May)
YTD (~) @PTD(Jan:May);

Assuming that the current month is May, add this formula to the QTD member:

@PTD(Apr:May);

And add this formula on the YTD member:

@PTD(Jan:May);

Essbase sums the values for the range of months, as appropriate. Opening Inventory has a time balance First tag, and Ending Inventory has a time balance Last tag. Essbase treats these values accordingly. See Calculate First, Last, and Average Values.

The following is an example of the calculation results for the members in the Inventory branch and for the Sales member:

Table 18-1 Results: Example Calculation Script for Calculating Period-to-Date Values

Measures->Time	Jan	Feb	Mar	Apr	May	QTD	YTD
Opening Inventory	100	110	120	110	140	110	100
Additions	110	120	100	160	180	340	670
Sales	100	110	110	130	190	320	640
Ending Inventory	110	120	110	140	130	130	130

The values for Sales and Additions have been summed.

Opening Inventory has a TB First tag. For QTD, Essbase takes the first value in the current quarter, which is Apr. For YTD, Essbase takes the first value in the year, which is Jan.

Ending Inventory has a TB Last tag. For QTD, Essbase takes the last value in the current quarter, which is May. For YTD, Essbase takes the last value in the year, which is also May.

Note:

You can also use Dynamic Time Series members to calculate period-to-date values.