Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BI-Geniuz
Advocate I
Advocate I

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?
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 )

 

 

 

 




DateClient   Customer    Rank    Perc Increase    (Wanted column)
 Revenue      
31-5-202158445AirBNB181,407264153123,8914212
30-4-202158445AirBNB175,025376557122,1721365
31-3-202158445AirBNB164,202165907116,3263018
28-2-202158445AirBNB150,355879778111,6352053
31-1-202158445AirBNB14-1,387187324111,239327
31-12-202058445AirBNB133,694360543112,8041316
30-11-202058445AirBNB129,614562086108,7852136
31-10-202058445AirBNB11-3,6890052999,2433957
30-9-202058445AirBNB10-1,737855123103,0447209
31-8-202058445AirBNB95,220483514104,8671602
31-7-202058445AirBNB83,64462773399,66420674
30-6-202058445AirBNB71,25859878596,15954914
31-5-202058445AirBNB63,83466139294,96432925
30-4-202058445AirBNB58,81062882691,45725326
31-3-202058445AirBNB4-10,9462543984,05176429
29-2-202058445AirBNB3-8,32695682794,38318817
31-1-202058445AirBNB20,331429668102,9563162
31-12-201958445AirBNB12,616215585102,6162156
1 ACCEPTED SOLUTION
parry2k
Super User
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

 

parry2k_0-1624663880016.png

 

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.

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

3 REPLIES 3
parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
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

 

parry2k_0-1624663880016.png

 

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.

 

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors