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

The following formula we show for a moving average uses a function similar to
the YTD function used before to compute Year-to-Date values, but is more flexible:
LastPeriods . We can think of YTD as returning a time range that always starts
at January 1 st , while moving averages need a dynamic range that starts at a given
number of periods before the current member. LastPeriods returns a range of time
periods of a given size, ending with a given member, which is exactly what we need
for our moving average calculation.
In some situations, the number of items in the moving window might be less than we
specified. At the start of our time dimension, or before sales for a particular product
have been recorded, the number of periods in our range will be smaller because
if we use a six month window, we might not have data for a whole six months. It
is important to decide what to show in these cases. In our example, we will show
empty values since, having fewer months, we consider the average not meaningful.
Let us start with a basic version of the calculation that will work at the month level:
CREATE
MEMBER CURRENTCUBE.Measures.AvgSixMonths AS
IIF (
Count (
NonEmpty (
LastPeriods (
6,
[Date Order].[Calendar].CurrentMember
),
Measures.[Sales Amount]
)
) <> 6,
NULL
Avg (
LastPeriods (
6,
[Date Order].[Calendar].CurrentMember
),
Measures.[Sales Amount]
)
),
FORMAT_STRING = "#,#.00" ;