Forum Discussion

vivek_rana's avatar
vivek_rana
Frequent Visitor
4 years ago
Solved

Average Growth Rate

How to calculate Average growth rate over time Dax query   Let say Number of milk cans sold by the company in the past four years: 2018 = 350 2019 = 450 2020 = 500 2021 = 650 Calculate ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    4 years ago

    Hi,

    The answer should be -7.18%.  You may download the PBI file from here.

    Hope this helps.

amitchandak's avatar
amitchandak
Super User
4 years ago

vivek_rana , using a separate date or year table

 

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

 

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

 

 

Now create Avg diff %

Avg diff % = AverageX(values('Date'[Year]), [diff %])