# Calculated Measure and MDX to calculate Average Weekly Change (AWC) for Time Series in Mondrian

Assuming we have a set of data points in a Weekly Time Series for a measure – TRXUnits. The data below shows the weekly data for a given Product.

 WeekName TRXUnits Week1 123 Week2 345 Week3 146 Week4 234 Week5 156 Week6 198 Week7 212 Week8 200 Week9 178 Week10 167 Week11 199 Week12 170 Week13 211

The Line Chart for the above Table Data – with a Least Square Fit line is shown below.

The AWC calculation Strategy is given below. Assume that the starting time for the above data is called T0 and the final time is called Tn

1. Assume that the equation of the least square fit line is – y = mx + C, where m is the slope of the line and C is the intercept.
2. Get the values of  y at Time T0 and  Tn.

Let these be :                                       yn = xn * m + C           &

y0 = x0 * m + C

AWC = ( yn – y0 ) = m * (xn – x0)

Functional Requirements for Comptuing AWC

This should be computable for any granularity for any dimension at any level.

For example – if we want to Calculate the AWC for the last 18 weeks of data here is the Calculated Measure – that is used to calculate the AWC in 2 steps.

The 1st Calculated Member is used to calculate the Slope of the line using the build in function called – LinRegSlope. This Calculated Measure uses the RANK function.

<CalculatedMember dimension=”Measures”

formula=”LinRegSlope( {LastPeriods( 18,  ([Period].[Week].Members.Item(18-1)  ) )  }, [Measures].[TRx], Rank([Period].[Week].CurrentMember,

LastPeriods( 18,  ([Period].[Week].Members.Item(18-1)  )    ) ))”>

</CalculatedMember>

The 2nd Calculated Member is then just used to multiply the Slope obtained from the 1st calculated measure and multiply it by ( 18 -1 ) – which essentially is ( yn-y0) – as derived in the formula above.

<CalculatedMember dimension=”Measures” formula=” ( [Measures].[ Slope18Week] *  (18-1) )”>

</CalculatedMember>

** MDX — Item function is Zero Based

Sample MDX Queries using the above Summary Variable

A sample MDX using the above Calculaated Measure would look like

select NON EMPTY {[Measures].[ AWC18] } ON COLUMNS,

NON EMPTY {[Alignment].[TerritoryName].Members} ON ROWS

from [Cube]