cancel
Showing results for
Did you mean:
Frequent Visitor

## Cumulative Revenue increase based on a column

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?

``````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
1 ACCEPTED SOLUTION
Super User

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

3 REPLIES 3
Super User

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

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

Announcements