Cumulative Growth index in DAX

[Anonymous]

Hello!

First time poster, long time follower. 

I am having a mind blank and for the life of me I cannot figure out the DAX formula to create this end result as shown in grey on the below screenshot.

These are mock numbers I have prepared in excel to show my desired result. In Power BI I already have two matrix tables showing the sales, as well as the Annual Growth % which I created using DAX.

But what I am really after is to show the cumulative growth trend as per the final table and chart, where the base value will always be 1 (selected from a year slicer) so that all regions begin from the same point and show the annual trend.

Any help would be much appreciated. Thank you!

[Anonymous]

Wow, what a work of art. Worked a treat. Thank you!

[Jihwan_Kim · Accepted solution]

Hi, please check the below.

All measures are in the sample pbix file's link down below.

Annual growth (cumulative index) : =
VAR currentperiodindex =
MAX ( Period[Index] )
VAR regiontable =
ADDCOLUMNS (
CROSSJOIN ( VALUES ( Region[Region] ), ALL ( Period[Index], Period[Period] ) ),
"@growth",
CALCULATE (
VAR currentsales = [Sales :]
VAR currentperiod =
MAX ( Period[Index] )
VAR previoussales =
CALCULATE (
[Sales :],
FILTER ( ALL ( Period ), Period[Index] = currentperiod - 1 )
)
VAR growth =
DIVIDE ( currentsales - previoussales, previoussales )
RETURN
IF ( SELECTEDVALUE ( Period[Index] ) = 1, 1, growth )
)
)
RETURN
SUMX ( FILTER ( regiontable, Period[Index] <= currentperiodindex ), [@growth] )

Link to the sample pbix file