Adding Calculations to the Cube - Expert Cube Development with SSAS Multidimensional Models - page 158

[Date Order].[Calendar].CurrentMember
),
Measures.[Sales Amount]
)
) <> 6,
NULL,
Avg (
LastPeriods (
6,
[Date Order].[Calendar].CurrentMember
),
Measures.[Sales Amount]
)
),
FORMAT_STRING = "#,#.00" ;
For more detail on implementing more complex moving averages, such as weighted
moving averages, take a look at the following post on Mosha Pasumansky's blog:
http://tinyurl.com/moshaavg .
Ranks
Often we'll want to create calculated measures that display rank values, for example,
the rank of a product based on its sales. Analysis Services has a function for exactly
this purpose: the Rank function, which returns the rank of a member inside a set. It's
important to realize that the Rank function does not sort the set it uses, so we'll have
to do that ourselves using the Order function as well.
Here's an example calculated measure that displays the rank of the current member
on the Products hierarchy within all of the members on the same level, based on the
Sales Amount measure:
CREATE
MEMBER CURRENTCUBE.Measures.ProductRank AS
IIF (
Measures.[Sales Amount] = 0,
NULL,
Rank (
Product.Products.CurrentMember,
Order (
Product.Products.CurrentMember.Level.MEMBERS,
Measures.[Sales Amount],
BDESC
)
)
);