MDX, SSAS 2005 calculating SPLY (Same Period Last Year)

1
votes

I'm trying to achieve the following: I have a measure: [Measures].[Sales] and a time dimmension: [Time].[Year - Month - Day]. I would like to add new measure: [Measures].[Sales SPLY] to work in the following way:

MONTH-----------------Sales--------------Sales SPLY

2009 January--------120 000,00--------110 000,00

2009 February ------100 000,00--------90 000,00

2009 March----------120 000,00--------110 000,00

TOTAL:---------------340 000,00--------290 000,00

The value of [Sales SPLY] is calculates using the following way: I created a new Sum measure, and in a Cube Script I replace it with the following code:

([Measures].[Sales SPLY], Leaves([Time])) = (PARALLELPERIOD([Time].[Year - Month - Day].[Year],1,[Time].[Year - Month - Day].currentmember), [MEASURES].[Sales])

It's working, however is just very, very slow. Does anyone know a better way of writing this MDX? using only the parrarel period in a calculated member will result in a bad value of the Total in SPLY measure. Please, I googled a lot and couldn't find any thing better than this.

1
sql-serverssasmdx

1 Answers

1
votes

I tend to just create a member like

WITH
MEMBER [Measures].[Sales SPLY] as ([Measures].[Sales], ParallelPeriod([Time].[Year - Month - Day].[Year],1,[Time].[Year - Month - Day].currentmember))
SELECT {[Measures].[Sales], [Measures].[Sales SPLY]} ON 0, ....

And it works fine.

How are you using it in your query overall?

Rob