0

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:

enter image description here

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?

Improve this question
2
  • I would guess that it has something to do with CurrentMember. Since your calculation is specifically looking for the current week and then the prior 51 weeks, if you feed it something that isn't a week (like a quarter), it is making a decision (probably based upon the default member) as to what the current week is and probably guessing wrong. – mmarie Mar 19 '15 at 17:28
  • I don't have time to write this out, but try rewriting your calculation so it uses a hierarchy that includes year/qtr/wk and gets the last child at the week level when fed anything other than a week. – mmarie Mar 19 '15 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.