Moving Aggregate Calculations on Dimensional Model

Here's a requirement to use moving aggregate calculations against a dimensional model and the solution to the same. The requirement is based on dates and hence may not be applicable to all scenarios.

Requirement: Display Quantity Sold, Revenue and Margin which is calculated as Revenue / Quantity for each year in a crosstab. Show the Margin Differential or moving-difference Calculation which is the (current year quantity - previous year quantity) / Margin of previous year.

Solution:

Step 1: Create a crosstab report with Years, Quantity Sold, Revenue dragged into it.

Step 2: Create a calculation to get Previous Year - lag(currentmember([Great Outdoors].[Years].[Years]),1). Let us call this as Prev Year.

Step 3: Create a Calculation called Quantity Diff - [Quantity Sold] - aggregate([Quantity Sold] with set [Prev Year])

Step 4: Create the Margin Diff Calc as [Qty Diff] / aggregate([Margin] within set [Prev Year]).

User Comments [Paul]: You could use the periodsToDate function for a running - aggregate([Quantity old] within set periodsToDate([All Member],currentMember([Great Outdoors].[Years].[Years])))