Stack Up Negative numbers every week until it becomes positive

[Anonymous]

hello everyone,
PBIX file : https://1drv.ms/u/s!Ag9tIyk2ofNRjlNhNyhsVv4AByS5?e=BAjhsShttps://1drv.ms/u/s!Ag9tIyk2ofNRjlKBgwru6jo...

I have gross profit table
Calendar table
investor table
and adjustment table

 

 

for first initial week i have those numbers simple.

i have the name, percentage, the gross profit and adjustment

payout = percentage * gross profit
current payout = adjustment + payout

my goal is to stack up the negative numbers

theoretically, when my weekly gross profit is  negative, i cant pay the investors and they shouldnt pay me

so how can i stack up the negatives for selected date even if its 2 week in a row

so example week 2:

see i also have negative value for current payout

and finally week 3:

current payout is positive

so in summary lets take investor A as an exaample:

week1 current payout = - 3350

week2 current payout = -100

week3 current payout = 600

so week 1 was neagtive so add this to week2 
so week2 must become -3350 + week2current payout which = -3350 -100 = -3450

im still negative so add it to week 3 so :
week3 current payout = 600 + stackedweek2 payout = 600 - 3450 = -2850

for week 4 it should satck up to  week 4 current payout is 2940 for investor A

week4 = week4 current payout + stackedof w1,w2 and w3 payout = 2940 - 2850 = 90

so for week 5 it shoudlnt stack anymore because its now positive so 

 

week5 = w5 currentpayout + stacked w1,w2,w3, and w4, = 600 + 0  = 600 since week4 was positive so no need to stack anymore until the current value is negative again

[Anonymous · Accepted solution]

Hi @Anonymous ,

According to your statement, I think your requirement likes looping in Power BI. As far as I know, Power BI doesn't support us to achieve looping by DAX. Here I suggest you to try M code to create custom columns to get result.

For reference:

List.Generate() and Looping in PowerQuery

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Oh thank you but i cant do it on m query because i use calculated measures to find gross profit and the rest. Thank you for your help though