Power pivot 2010 - Horizntal %

[LearnMeExcel]

how i can create growth Percentage for year to year
i put year inColumn Labl
in my data thers many year from 2000 to 2014
i want when i select 2013 and 2014
i use this muesure
=DIVIDE(CALCULATE([Total Sales], SalesT [InYear]=2014),CALCULATE([Total Sales],SalesT[InYear]=2013))-1
but this is ony for 2014
if i select by slicr from 2011 to 2014
to give me % for every year from the previous one

i hope my idea is clear

thax

 

[scottsen]

You will want a first class Calendar table (which unfortunately, power pivot doesn't really make SUPER easy -- you need to create your own in excel/powerpivot/sql), then relate that Calendar table to your Sales.

You would then have measures like:
[Total Sales - PY] := CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Calendar[DateKey]))
[YoY Sales Growth] := DIVIDE([Total Sales] - [Total Sales - PY], [Total Sales - PY])

Even if you have a custom calendar (445 or whatever), you will STILL want a calendar table, though... you won't be able to use SAMEPERIODLASTYEAR and stuff like that. It would be something like...
[Total Sales - PY] := CALCULATE([Total Sales], FILTER(ALL(Calendar), Calendar[Year] = MAX(Calendar[Year]) - 1)