YOY Growth Showing -100%

[ec1957]

All

Calculating YOY growth, 2014 over 2013 using powerpivot, . The calculation works just fine, however, in a pivot the following happens when there is a value for 2013 and not for 2014. Example

Year 2013
Cust1 value of 50
Cust2 value of 100
Cust3 value of 200

Year 2014
Cust1 value of 100
Cust2 value of 200

My pivot will return the following;

Cust1 100%
Cust2 100%
Cust3 -100%

Since Cust3 did not do business in 2014, I don't want it showing up in the pivot with a -100%. Any thoughts would be appreciated

=CALCULATE([SumGross],DATEADD(Calendar[Date], -1,Year))

Which feeds my YOY calculation;

=([Total Gross YTD]-[Total Gross LY])/[Total Gross LY]

I'm a real newby to this powerpivot thing, any help would be appreciated. Thank you.

 

[scottsen]

The easiest is probably
=IF ([Total Gross YTD] = BLANK(), BLANK(), ([Total Gross YTD]-[Total Gross LY])/[Total Gross LY])

Note you should also convert A/B to DIVIDE(A,B) so that when B is 0/blank, you get a BLANK() instead of an error.