This project has moved. For the latest updates, please go here.

Groups

Aug 28, 2012 at 1:43 PM

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 2: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 well-defined period like Q1.

Marked as answer by furmangg on 6/9/2014 at 12:34 AM