Annual average between period of years

[Anonymous]

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%.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

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]))

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.

[Anonymous]

Hey @v-zhangti 

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

[pmreis]

Hi @Anonymous 

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