Cumulative Revenue increase based on a column

[BI-Geniuz]

Hi guys, 

I'm struggling with an issue and hopefully someone can help me out.

Basically I have a table that has a report date (monthly) a client and I've added a rank based on the report date.
So I want to enter an initial number (like 100 in this example) and calculate the revenue based on the column [Perc Increase].

So for Index 1 it's 100 + 2,616% = 102.616
And for index 2 it's 102.616 + 0,331% = 102,956
And for index 3 it's 102,956 + -8,326% = 94,383

I've tried using this formula, but it get's stuck on only one row. So hopefully someone here knows the magic?
Thanks in advance!

Revenue = 
VAR prev =
    CALCULATE (
        MAX ( Sheet1[Initial] );
        ALL ( Sheet1 );
        FILTER (
            Sheet1;
            Sheet1[Client] = EARLIER ( Sheet1[Client] )
                && Sheet1[Rank]
                    = EARLIER ( Sheet1[Rank] ) - 1
        )
    )
RETURN
    prev + prev * ( Sheet1[Perc Increase] / 100 )

Date	Client	Customer	Rank	Perc Increase	(Wanted column)  Revenue
31-5-2021	58445	AirBNB	18	1,407264153	123,8914212
30-4-2021	58445	AirBNB	17	5,025376557	122,1721365
31-3-2021	58445	AirBNB	16	4,202165907	116,3263018
28-2-2021	58445	AirBNB	15	0,355879778	111,6352053
31-1-2021	58445	AirBNB	14	-1,387187324	111,239327
31-12-2020	58445	AirBNB	13	3,694360543	112,8041316
30-11-2020	58445	AirBNB	12	9,614562086	108,7852136
31-10-2020	58445	AirBNB	11	-3,68900529	99,2433957
30-9-2020	58445	AirBNB	10	-1,737855123	103,0447209
31-8-2020	58445	AirBNB	9	5,220483514	104,8671602
31-7-2020	58445	AirBNB	8	3,644627733	99,66420674
30-6-2020	58445	AirBNB	7	1,258598785	96,15954914
31-5-2020	58445	AirBNB	6	3,834661392	94,96432925
30-4-2020	58445	AirBNB	5	8,810628826	91,45725326
31-3-2020	58445	AirBNB	4	-10,94625439	84,05176429
29-2-2020	58445	AirBNB	3	-8,326956827	94,38318817
31-1-2020	58445	AirBNB	2	0,331429668	102,9563162
31-12-2019	58445	AirBNB	1	2,616215585	102,6162156

[parry2k]

@BI-Geniuz Glad to hear that it is working for you. Cheers!!

[parry2k · Accepted solution]

@BI-Geniuz here is the measure, tweak it as you see fit.

Data1 = 
VAR __initialValue = 100
RETURN
__initialValue *
 (
    PRODUCTX ( 
        FILTER ( ALL ( 'Table' ), 'Table'[Rank] <= MAX ( 'Table'[Rank] ) ),
        ( 1 + 
            CALCULATE ( 
                MAX ( 'Table'[Perc Increase] ) / 100
            ) 
        ) 
    ) )

ouput

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[BI-Geniuz]

Thanks a lot Parry, I never came across the ProductX statement! Great job and keep up the good work, you've saved my weekend!! 😃