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
gustavofarias10
New Member

Annual average between period of years

I have filtered a certain range of years that present the following data:

year 2015 = 72
year 2016 = 77
year 2017 = 61
year 2018 = 97

I already have the following data:
variation in the period: 34.7%
variation in the last year: 6.5%

But I need to know how to arrive at the annual average, which in this case is equal to 10.4%.anexo.png

 

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @gustavofarias10 

 

You can try the following methods.

Measure % = 
Var _current=SUM('Table'[Value])
Var _Previous=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year])-1))
Return
DIVIDE(_current-_Previous,_current)
Average% = 
Var _table=SUMMARIZE('Table','Table'[Year],"%",[Measure %])
Return
DIVIDE(SUMX(FILTER(_table,[Year]>=MIN('Table'[Year])&&[Year]<=MAX('Table'[Year])),[%]),MAX('Table'[Year])-MIN('Table'[Year]))

vzhangti_0-1699868329905.png

Is this the result you expect? Please see the attached document.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

Hi, @gustavofarias10 

 

You can try the following methods.

Measure % = 
Var _current=SUM('Table'[Value])
Var _Previous=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year])-1))
Return
DIVIDE(_current-_Previous,_current)
Average% = 
Var _table=SUMMARIZE('Table','Table'[Year],"%",[Measure %])
Return
DIVIDE(SUMX(FILTER(_table,[Year]>=MIN('Table'[Year])&&[Year]<=MAX('Table'[Year])),[%]),MAX('Table'[Year])-MIN('Table'[Year]))

vzhangti_0-1699868329905.png

Is this the result you expect? Please see the attached document.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hey @v-zhangti 

Your solution ended up clarifying an idea that will help me solve this problem. Thank you very much.

pmreis
Super User
Super User

Hi @gustavofarias10 

Here is a DAX Formula:

CAGR = 
VAR StartingYear = MIN('TableName'[YearColumn])
VAR EndingYear = MAX('TableName'[YearColumn])
VAR StartingValue = CALCULATE(SUM('TableName'[ValueColumn]), 'TableName'[YearColumn] = StartingYear)
VAR EndingValue = CALCULATE(SUM('TableName'[ValueColumn]), 'TableName'[YearColumn] = EndingYear)
VAR NumberOfYears = EndingYear - StartingYear
RETURN
IF(
    NumberOfYears > 0,
    POWER(EndingValue / StartingValue, 1 / NumberOfYears) - 1,
    BLANK()
)


Please replace with the correct table names, and it should work.


Pedro Reis - Data Platform MVP / MCT
Making Power BI and Fabric Simple

If my response resolved your issue, please mark it as a solution to help others find it. If you found it helpful, please consider giving it a kudos. Your feedback is highly appreciated!

Find me at LinkedIn

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.