# Why is MDX Moving Average Calculation Inconsistent in Excel?

This question hurts my brain to talk about. Hopefully I can explain it properly.

I have the following calculation defined in my cube:

``````CREATE MEMBER CURRENTCUBE.[Measures].[52 Week Comp Net Sales Total]
AS  SUM(
{[Dim Fiscal Weeks].[Week Id].CurrentMember.Lag(51) :
[Dim Fiscal Weeks].    [Week Id].CurrentMember}
,[Measures].[Comp Net Sales]),
FORMAT_STRING = "#,##0.00;-#,##0.00",
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Sales / Trans / Units';
``````

This ought to give me a 52-week trailing sum of sales, and it does... but ONLY when the cube is expanded to a weekly view in Excel. When it's rolled up to a Quarterly or YTD view, then it displays the sum of sales that occurred in the rolled-up-period only.

Here is a screen shot explaining what I mean:

What I don't understand is why the Quarter and Period (month) roll-ups don't include a full-year's worth of data. If a year's worth of sales trailing back from 1/1/2005 was 954,000, it doesn't make sense that Quarter 1, 2005 shows a year's worth of sales is only 252,000.

Can someone help me understand what I'm seeing? Have I done something wrong? How do I write a rolling average calculation that is accurate even when rolled up?