MDX - Cube Calculated member sliding 12 month comparison with previous month data

Question

Post reply

MDX - Cube Calculated member sliding 12 month comparison with previous month data

iBar

SSCertifiable

Points: 5872
More actions
August 7, 2012 at 10:32 am

#260441

Hi,
I want to compare Aggregate of following two different resultsets:
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON Empty
{
LastPeriods(
12, [Date].[Calendar].[Month].[January 2008]
)
}
On Rows
FROM
[Adventure Works];
GO
SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
NON Empty
{
LastPeriods(
12, [Date].[Calendar].[Month].[February 2008]
)
}
On Rows
FROM
[Adventure Works];
GO
Now actually i want to add a calculated member in the cube in a way that in Current Month, it compares aggregated sales of previous 12 months with the aggregate of previous 12 month from one month before previous month.
For example
In march 2008
Aggregate(Mar2007 .... Feb2008) ,
Aggregate(Feb2007 .... Jan2008) ,
Aggregate(Mar2007 .... Feb2008) - Aggregate(Feb2007 .... Jan2008) AS Rolling_12Month_Growth.
Note: it is different from Month-Over-Month Growth.
So any clue please?
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

iBar SSCertifiable Points: 5872 More actions · Answer 1

Hi Moderator,

This is my second MDX question in "Analysis Services" forum of SSC and NO answer at all.

Am i on the right place for such questions? or what else?

Thanks.

iBar SSCertifiable Points: 5872 More actions · Answer 2

The best help i found on this question is available on following Link. Thanks to Pavel Pawlowski. Excellent work.

http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/1d00b9fd-80f9-493c-8d04-b125882a6e5c/#1d00b9fd-80f9-493c-8d04-b125882a6e5c

Thanks and Cheers.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

This is the right place, but we don't have a lot of MDX posters, so there might not be anyone that can easily answer.

happycat59 One Orange Chip Points: 29389 More actions · Answer 4

I think that the following will give you what you are after

WITH MEMBER Measures.CurrentMonth AS 'SUM ( [Date].[Calendar].Currentmember.lag(11) : [Date].[Calendar].Currentmember, [Measures].[Internet Sales Amount])'

MEMBER Measures.PriorMonth AS 'SUM ( [Date].[Calendar].Currentmember.lag(12) : [Date].[Calendar].Currentmember.lag(1), [Measures].[Internet Sales Amount])'

Member Measures.Difference AS 'Measures.CurrentMonth - Measures.PriorMonth'

SELECT

{measures.currentmonth, measures.priormonth, Measures.Difference} ON COLUMNS

FROM

[Adventure Works]

where ([Date].[Calendar].[Month].[February 2004])