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 the company's average annual growth rate.

So, if the present value is 650, the past value is 350 and the number of years is 4, you get:

Growth rate after 2018: (450 - 350) / 350 x 100 = 28.57%

Growth rate after 2019: (500 - 450) / 450 x 100 = 11.11%

Growth rate after 2020: (650 - 500) / 500 x 100 = 30%

Average growth rate over time = (28.57% + 11.11% + 30%) / 3 = 23.22% per year

Im struggling to get this in dax query .
Please Suggest a method to get Average growth rate over time

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.

12 Replies

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

    • vivek_rana's avatar
      vivek_rana
      Frequent Visitor
      4 years ago

      Hi amitchandak 

       

      How can we do it at Year -month level. I tried replacing Year with Yearmonth field from calender table..but seems like its not working... Can you please suggest here.