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?