PREV

Syntax

PREV (Dimension, Data, {Count})

Description

The PREV function returns the value of Data from Count members back in Dimension. If Count is omitted, it is assumed to be 1.

Note:

The PREV function operates on detail member names that are persisted in the main record. This function does not use trees to determine the order of members.

Returns

The PREV function returns the value of Data from Count members back in Dimension. If Count is omitted, it is assumed to be 1.

Example

To refer to the previous month's sales in a formula, use PREV(MONTHS, SALES).

Suppose that you want to forecast the total monthly receipts for a company, assuming that some of each month's sales are received immediately, some are received in one month, some are received in two months, and some are received in three months. First, define data cubes that contain the estimated percentage of sales received for each time period: PCT_RECV_IMMEDIATELY, PCT_RECV_IN_1_MONTH, PCT_RECV_IN_2_MONTHS, PCT_RECV_IN_3_MONTHS. Next, define a monthly data cube called SALES that contains the sales forecast for each month. Calculate the TOTAL_MONTHLY_RECEIPTS data cube with these formulas:

  • RECV_IMMEDIATELY data cube formula:

    PCT_RECV_IMMEDIATELY * SALES

  • RECV_IN_1_MONTH data cube formula:

    PCT_RECV_IN_1_MONTH * PREV(MONTHS, SALES)

  • RECV_IN_2_MONTHS data cube formula:

    PCT_RECV_IN_2_MONTHS * PREV(MONTHS, SALES, 2)

  • RECV_IN_3_MONTHS data cube formula:

    PCT_RECV_IN_2_MONTHS * PREV(MONTHS, SALES, 3)

  • TOTAL_MONTHLY_RECEIPTS data cube formula:

    RECV_IMMEDIATELY + RECV_IN_1_MONTH + RECV_IN_2_MONTHS + RECV_IN_3_MONTHS

RECV_IMMEDIATELY contains the amount received from the current month's sales, RECV_IN_1_MONTH contains the amount received from the previous month's sales, and so on. Add all of these amounts together to calculate the total receipts for the month.