# Year on year growth but only for active records

• ### Question

• Greetings from the UK hopefully someone can help me with my MDX as I am not as good as I need to be.

I am looking  acube of data for a set of houses and need to calculate % year on year growth in charge for the base of units that where in scope at the start of the measurement and are still live at the end.

I have :

1.     A measure [Measures].[Annual Charge]

2..    A Calculated member of charge for last year  [AnnualChargePreviousYear]

sum({ParallelPeriod([Time].[Calendar].[English Month Name], 12,

[Time].[Calendar].CurrentMember)},[Measures].[Annual Charge])

3. A Change in totals year on year calculated member [AnnualChargeChangeYear]

Case

When IsEmpty ([Measures].[Annual Charge] ) Then Null

When IsEmpty ([Measures].[AnnualChargePreviousYear] ) Then Null

When ([Measures].[Annual Charge]) = 0 Then null

When ([Measures].[AnnualChargePreviousYear]) = 0 Then null

Else ([Measures].[Annual Charge]-[Measures].[AnnualChargePreviousYear])/[Measures].[AnnualChargePreviousYear]

When I calculate the % change from 3 I get the following which is correct  as a change in the Total of the annual charge

 English Month Name [AnnualChargePreviousYear] [Annual Charge] [AnnualChargeChangeYear] June 2012 526,971 549,023 4.18% July 2012 521,199 552,353 5.98% August 2012 527,463 554,489 5.12% September 2012 537,133 556,397 3.59% October 2012 544,967 550,097 0.94% November 2012 546,167 536,973 -1.68% December 2012 547,805 537,723 -1.84% January 2013 551,505 538,083 -2.43% February 2013 549,253 556,001 1.23% March 2013 541,777 543,619 0.34% April 2013 548,951 544,485 -0.81% May 2013 548,951 539,149 -1.79% June 2013 549,023 533,779 -2.78%

But what I need is the change only where the unit record was there in the previous year and is still active in the current period.

And am unsure if I should use  “nonempty”, “Exists” or another function:

To get the Year on Year movement for active records, using this post

I have tried:

with MEMBER [Measures].[yoy] AS

SUM(

NonEmpty(NonEmpty([Property Details].[PropertyID].[PropertyID].MEMBERS, [Measures].[Annual Charge])

, ([Time].[Calendar Year].PrevMember, [Measures].[Annual Charge]))

,  ([Time].[Calendar Year].CurrentMember, ([Measures].[Annual Charge])

-([Time].[Calendar Year].PrevMember,[Measures].[Annual Charge])))

Where I am trying to find only those records where the property ID was  present a year ago then doing just the charge change calculation but to no avail as this sums to a year when I need the annual change but by month, replicating the results table above but only for properties live at both the start and end

Any ideas gratfully received. Apologies if this is unclear but I am really stuck with this.

Regards

Kevin

Still bad at golf but trying to get better and now officailly rubbish at playing guitar...

Monday, July 1, 2013 12:59 PM

### All replies

Thank you for your post. I am currently looking into this issue and will give you an update as soon as possible.

Best Regards,

Elvis Long
TechNet Community Support

Wednesday, July 3, 2013 9:41 AM

• Hi Kevin,

Please try this script, I used two calculated measures:

with MEMBER [Measures].[prevYear] AS
(
[Measures].[Annual Charge],
ParallelPeriod  (
[Time].[Calendar Year].[Year],   -- year might be called different in your cube
1,
[Time].[Calendar Year].CurrentMember
)
)

Member [Measures].[YoY] AS
(
IIF (
[Measures].[prevYear]=0,
NULL,
-- here you could also use a calculation to have this inpercentage, or use an IIF to check [Measures].[Annual Charge] too
([Measures].[Annual Charge]- [Measures].[prevYear])
)
)

Also check out this description from Chris Webb
http://wiki.pentaho.com/display/COM/MDX+Calculations+-+Previous+Period+Growth

Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Thanks,
Orsi
Microsoft Online Community Support

Tuesday, July 16, 2013 2:39 PM