Forum Discussion

ShivGC's avatar
ShivGC
Helper I
3 years ago
Solved

Growth Rate Function using Productx (Dynamic)

Hi, 

 

I am trying to get a DAX measure that is able to look at the earliest SELECTED year, set that year to 100, and then continue to calculate the growth rate from that year onwards. 

For instance, currently, I am able to get a table like this:

Table Name: table1

YearGrowth (%)
2007

 

20083.6
20092.2
2010

3.3

2011

4.5

20122.8
20132.6
20141.5
20150
20160.7

 

And use this formula:

 
Growth Calculation =
100 *
CALCULATE(
    PRODUCTX(
        'table 1',
        1+'table 1'[Growth (%)]
    ),
    FILTER(
        ALL('table 1'),
        'table 1'[Year]<=MAX('table 1'[Year])
        )
)



To get this result:

YearGrowth (%)Growth Calculation
2007

 

100

20083.6103.6
20092.2105.88
2010

3.3

109.37

2011

4.5

114.30

20122.8117.50
20132.6120.55
20141.5122.36
20150122.36
20160.7123.21

 

Although, this is exactly the result I am looking for, I also want it to be dynamic. So, if I change the range of years to 2010 - 2015, then the base year would be 2009 at 100, and the last year of accumulation would be 2015!

Does anyone know how to do this?

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ShivGC 

    Do you mean if you select 2010-2015, then the value of 2010 should be set as 100, then accumulate it to 2015? If you want to achieve this, you can refer to the following measure

    Measure = var a=MINX(ALLSELECTED(Table1),[Year])
var b=MAXX(ALLSELECTED(Table1),[Year])
return IF(SELECTEDVALUE(Table1[Year])=a,100,100*PRODUCTX(FILTER(ALLSELECTED(Table1),[Year]<=SELECTEDVALUE(Table1[Year])&&[Year]>a),[Growth(%)]/100+1)
)

    Output

     

    Best Regards!

    Yolo Zhu

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

     

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ShivGC 

    Do you mean if you select 2010-2015, then the value of 2010 should be set as 100, then accumulate it to 2015? If you want to achieve this, you can refer to the following measure

    Measure = var a=MINX(ALLSELECTED(Table1),[Year])
var b=MAXX(ALLSELECTED(Table1),[Year])
return IF(SELECTEDVALUE(Table1[Year])=a,100,100*PRODUCTX(FILTER(ALLSELECTED(Table1),[Year]<=SELECTEDVALUE(Table1[Year])&&[Year]>a),[Growth(%)]/100+1)
)

    Output

     

    Best Regards!

    Yolo Zhu

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

     

     

    • ShivGC's avatar
      ShivGC
      Helper I
      3 years ago

      Wow, 

      Thank you for the response. This works exactly how I would like after making two small tweaks. 

      If possible could you please explain, this bit of the code to me?

       

      [Year]<=SELECTEDVALUE(Table1[Year])&&[Year]>a