MDX in Analysis Services: Measuring Change over Time

, [Amount]

The final expression appears in Illustration 19 below:

19.  Click OK to accept the expression as displayed.

The results dataset appears in the Data Viewing pane as depicted in Illustration 20.

We can readily see that the values that appear in the Prior Amount column accurately reflect the prior period balance, as shown in the associated previous time period slot in the Amount column. For example, the Q4, Month 11 Prior Amount ($ 28,448.04) matches the Q4, Month 10 Amount. The same applies to the Quarter level rollups (except for periods for which the cube does not contain data for a prior period / a respective time member).

Now let's repeat the process for the Average Total Cost Per Unit calculated member.

The Calculated Member Builder appears.

The Value Expression box of the Calculated Member Builder now appears as shown in Illustration 21.

, [Avg Total Cost Per Unit]

The final expression appears in Illustration 22 below:

Let's free up some real estate on the screen.

The Properties pane - Advanced tab appears as depicted in Illustration 23.

The new results dataset appears in the Data Viewing pane, as depicted in Illustration 24.

As we noted in our earlier example, we can see that the values that appear in the Prior Avg column accurately reflect the prior period balance, as shown in the associated previous time period slot in the Avg Total Cost Per Unit column. For example, the Q4 Total Prior Avg ($ 1.50) matches the Q3 Total Avg Cost Per Unit value. The same applies to the Year level rollups (both the 1997 Total Avg Cost Per Unit and 1998 Total Prior Avg rows reflect $ 1.49).

MDX handles "parallel period" time considerations in much the same fundamental way: through the comprehension of hierarchical relationships, which was built into its design. Let's take a look at how we can leverage this functionality yet further in the final section of this lesson.