Calculate cumulative percentage change

• scottcabral

  Default port

  Points: 1495

  More actions

  April 24, 2014 at 5:50 am

  #306906

  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

• PB_BI

  SSCoach

  Points: 17461

  More actions

  April 25, 2014 at 4:38 am

  #1708714

  Something along the lines of

  WITH MEMBER

  [Cumulative Total] AS SUM({NULL:[Date].[Quarter].CURRENTMEMBER}, [Your Measure])

  would do it, obviously change it to suit your environment 🙂

  Sorry I misread the post, the above may give you the foundation of what's needed though.

  ---

  I'm on LinkedIn