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

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

1 ACCEPTED SOLUTION
Community Support

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.

3 REPLIES 3
Community Support

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.

New Member

Hey @v-zhangti

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

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!