calculate cumulative percentage change using MDX

Question

Hi,

I was able to create an MDX calculation that generates percentage changes between time periods for a numeric measure.

However, what I really need is to calculate a cumulative percentage between time periods for the same measure.

Let's say I have a Client dimension with Client ABZ.  I also have a Date dimension and I want to calculate the cumulative premium change percentage over Quarters.

So here are the premium values for each quarter for Client ABZ:

Q1: $100

Q2: $120

Q3: $140

Q4: $110

So Q1 will start off at 0% since there is nothing before Q1.  Q2 will have a 20% increase so the cumulative percentage for Q2 will be 20%.  Q3 changed 16.6%, so this will need to be added to the 20% from the Quarter before (Q2) and the new cumulative percentage for Q3 will be 36.6%.  Q4 changed by - 21.4% from the previous quarter and will need to be subtracted from Q3 percentage to bring the new cumulative percentage in Q4 to 15.2%.  So it's like keeping a rolling percantage amount for each time period (in this case it would be quarter)

Is there a way to do this in SSAS using an MDX calculated field?

thanks

Scott

Answer 1

I am not sure if I totally understood what you are asking for.

Here's how I would do.

with 

members measures.sales_current as sales

members measures.sales_prev_qtr as (sales,parallelperiod(date.quarter,1,date.date))

members measures.sales_cum1 as (measures.sales_prev_qtr + measures.sales_current)

members measures.sales_cummulative as [sales_cum1+ sales_prev_qtr]

select measures.sales_cummulative on 0

, date.quarter on 1

from [cube_name]

The above calculation gives you cummulative sales quarter by quarter and if you manipulate the above, I hope you will be able to get what you want.

Thanks

Ayush

Answer 2

Try if this helps

WITH MEMBER [Measures].[QrtCum%]
AS
IIF(([Measures].[Sales Amount],[Date].[Calendar Quarter].CURRENTMEMBER.LAG(1))=0
OR ([Measures].[Sales Amount],[Date].[Calendar Quarter].CURRENTMEMBER) =0 ,0,
((([Measures].[Sales Amount],[Date].[Calendar Quarter].CURRENTMEMBER)-
([Measures].[Sales Amount],[Date].[Calendar Quarter].CURRENTMEMBER.LAG(1)))/
([Measures].[Sales Amount],[Date].[Calendar Quarter].CURRENTMEMBER.LAG(1)))*100),
FORMAT_STRING = "#,#.00"

SELECT
{[Measures].[Sales Amount],[Measures].[QrtCum%]} ON 0,
[Date].[Calendar Quarter].[Calendar Quarter] ON 1
FROM [Sales]

If required create a similar calculated member.

---

Saurabh Kamath