For example, assume that a database contains assets data values that are stored on a month-by-month basis. You can calculate the difference between the assets values of successive months (the asset movement) by subtracting the previous month’s value from the present month’s value.

Assume these three members manage the asset values for the database:

• Assets for the monthly asset values

• Asset_MVNT for the asset movement values

• Opening_Balance for the asset value at the beginning of the year

For Jan, the Asset_MVNT value is calculated by subtracting the Opening_Balance value from the Jan value.

You would add this formula on the Asset_MVNT member:

IF(@ISMBR(Jan)) Asset_MVNT = Assets - Opening_Balance;
   ELSE Asset_MVNT = Assets - @PRIOR(Assets);
ENDIF;

The following table shows the results when Essbase calculates the difference between the values of assets in successive months:

Essbase cycles through the months, performing these calculations:

1. The IF statement and @ISMBR function check whether the current member on the Year dimension is Jan. This check is necessary because the Asset_MVNT value for Jan cannot be calculated by subtracting the previous month’s value.

2. If the current member on the Year dimension is Jan, Essbase subtracts the Opening_Balance from the Jan -> Assets value and places the result in Jan -> Asset_MVNT.

3. If the current member on the Year dimension is not Jan, the @PRIOR function obtains the value for the previous month’s assets. Essbase subtracts the previous month’s assets from the current month’s assets. It places the result in the current month’s Asset_MVNT value.