Growth Rate Function using Productx (Dynamic)

[ShivGC]

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

Year	Growth (%)
2007
2008	3.6
2009	2.2
2010	3.3
2011	4.5
2012	2.8
2013	2.6
2014	1.5
2015	0
2016	0.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:

Year	Growth (%)	Growth Calculation
2007		100
2008	3.6	103.6
2009	2.2	105.88
2010	3.3	109.37
2011	4.5	114.30
2012	2.8	117.50
2013	2.6	120.55
2014	1.5	122.36
2015	0	122.36
2016	0.7	123.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 · Accepted solution]

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]

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