Forum Discussion

BI-Geniuz's avatar
BI-Geniuz
Advocate I
5 years ago
Solved

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
Need Help
  • parry2k's avatar
    parry2k
    5 years ago

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

     

     

     

3 Replies

  • parry2k's avatar
    parry2k
    Super User
    5 years ago

    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's avatar
      BI-Geniuz
      Advocate I
      5 years ago

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