Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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

201717227
201729974
2017310000
201613098
201623667
201631059
201618414
201525247
201538625
201518114
201426706
201438640

 

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:
image.png
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
v-shex-msft
Community Support
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())

1.PNG

 

 

Regards,

Xiaoxin Sheng

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

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
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())

1.PNG

 

 

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.