
I'd like to add a quarters measure that summed the three prior months of data. We currently have weekly data in the cube that is rolled up to Monthly and they Yearly. And the Quarters are off cycle  Q1 is July Aug Sep
I tried
[Measure][Q]
aggregate([Time].[CurrentMember].Lag(3):[Time].[CurrentMember],[Measures].[Cost]),NULL)
It adds a column in each period (month) and doesn't add the values. Any clue on the proper syntax would be greatly appreciated  if it can be managed at all via OLAP Pivot Table Extensions.
Thanks  John


Coordinator
Aug 28, 2012 at 3:58 PM

If you name the calculation: [Measures].[Q]
And use this expression:
Aggregate([Time].CurrentMember.Lag(3):[Time].CurrentMember.Lag(1),[Measures].[Cost])
What do you get? That should be a rolling prior 3 months of data. In other words, in the October column, that should show you the sum of July+Aug+Sep.
I would highly, highly, highly recommend you talk with your cube developer and have them add in a Quarter level to your cube. Doing all this in MDX is not optimal if you're just wanting the rollup for a welldefined period like Q1.

