This post is a part of a series of blog posts I am writing to give you a Batman-like Utility Belt of MDX calculations.With this Utility Belt available you can have a template of commonly needed calculations you can use for just about any Analysis Services project.

In part one of this series I showed how you could use the function ParallelPeriod to return sales from the previous year at all levels (Year, Quarter, Month).

In this series I present the business problem, a general solution, and the calculation to solve that problem.

Problem

To show the sales for the previous date member. (Ex. Show the sales for the last year, last quarter, or last month)

Solution

Using a function called PrevMember you can return the value of previous member.

I have the following similar equation where the first [Date] is the name of the date dimension, [Fiscal Calendar] is the hierarchy, the next [Date] is the name of the field with the individual dates. [Fresh Caustic End Inv] is the value of the inventory stored in the Measure group. The formula below was supposed to calculate the beginning inventory (i.e., the previous days ending inventory). It returns #VALUE# -- what do I have wrong?
([Date].[Fiscal Calendar].[Date].PrevMember,[Measures].[Fresh Caustic End Inv])

To follow up with everybody..... here is the final formula that worked:
([Date].[Fiscal Calendar].CurrentMember.PrevMember,[Measures].[Fresh Caustic End Inv])

## Comments (3)