Sum vs Previous Sum By Years, %

[Death4Free]

Hi! How can I calculate measure in pivot table - Sales Vs Prev. sales, % ( there could be more years, and i have Calendar Master if it helps)?

	2016		2017		2018
	Sales	Sales Vs Prev. sales, %	Sales	Sales Vs Prev. sales, %	Sales	Sales Vs Prev. sales, %
dec	10	100%	12	120%	10	83%
jan	9	100%	6	66%	8	133%
feb	15	100%	16	106%	18	113%

[juraj_misina · Accepted solution]

Hi,

you can use Before() and Alt() functions for this.

Sales = Sum(Sales)

Sales vs Prev. sales, % = Alt(Sum(Sales)/Before(Sum(Sales)), 1)

Before wil calculate Sum(Sales) using previous dimension value (previous year in your case), but in the first column it will return null (because there's no previous value), therefore you need to combine it with Alt(), witch returns first valid value from a list of parameters.

Hope this helps.

Juraj

[Death4Free]

Thank you for so fast answer!