cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
4 REPLIES 4
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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
Not applicable

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
Not applicable

Thank you @v-shex-msft, works like a charm!