How to calculate the percentage growth by years using one single measure?

[Anonymous]

How to calculate percentage of sales growth by year using an unique measure on Dax? For example, I the following table:

Year      Month        Profit

2017	1	7227
2017	2	9974
2017	3	10000
2016	1	3098
2016	2	3667
2016	3	1059
2016	1	8414
2015	2	5247
2015	3	8625
2015	1	8114
2014	2	6706
2014	3	8640

I want to create an unique masure that shows me the growth of my sales between two years. Currently I have to create a measure two compare all of the year, like this:

// Measure 1 - comparing 2014 and 2015
2014_2015 = 
VAR
	_2014 = CALCULATE(SUM(Table1[Profit]); Table1[Year] = 2014)
VAR
	_2015 = CALCULATE(SUM(Table1[Profit]); Table1[Year] = 2015)
RETURN
	(_2015 / _2014) - 1

// Measure 2 - comparing 2015 and 2016
2015_2016 = 
VAR
	_2015 = CALCULATE(SUM(Table1[Profit]); Table1[Year] = 2015)
VAR
	_2016 = CALCULATE(SUM(Table1[Profit]); Table1[Year] = 2016)
RETURN
	(_2016 / _2015) - 1

// Measure 3 - comparing 2016 and 2017
2016_2017 = 
VAR
	_2016 = CALCULATE(SUM(Table1[Profit]); Table1[Year] = 2016)
VAR
	_2017 = CALCULATE(SUM(Table1[Profit]); Table1[Year] = 2017)
RETURN
	(_2017 / _2016) - 1

If I want to show a matrix with that percentages I got this:
But this is uggly and wierd, so is it possible to do what I want? Does not have to be a single measure, I openned for new ideas, like filter and measure comabinations.  

Thanks.

[Anonymous · Accepted solution]

Hi @Anonymous,

You can try to use below formula to calculate the rolling growth.

Measure:

Year Growth = 
VAR _previous = CALCULATE(SUM(Sheet3[Profit]),FILTER(ALLSELECTED(Sheet3), Sheet3[Year] = MAX(Sheet3[Year]) -1))
VAR _current =  CALCULATE(SUM(Sheet3[Profit]),FILTER(ALLSELECTED(Sheet3),Sheet3[Year] =MAX(Sheet3[Year])))
return
IF(_previous<>BLANK(),DIVIDE(_current,_previous,0)-1,BLANK())

Regards,

Xiaoxin Sheng

[Anonymous]

Hi Xiaoxin, thank you for the post.

But the percent growth for total seems to be wrong. Any ideas to fix it?

Regards,

Julien

[Anonymous]

Hi Xiaoxin, thanks for the post, it will help me also a great deal.

why would your Grw 5 for total and 2017 be the same (0.68), the 2017 appear to be incorrect, let me know- thanks

[Anonymous]

Thank you @Anonymous, works like a charm!