I have been working on this problem for a while and I am open to all suggestions!
The product has a re-occurring income model which takes a % of the vending amount as well as a flat fee every month, the user gets paid out the remainder of the vending amount. The product can consume an amount which is less than the flat which creates a "debt" this debt is then carried over to the next transaction to be re-couped.
The "debt" can then be recovered as we see below in transactions occuring.
Product 1 -
Serial Number
Date
Vending
Monthly Rate
PERCENTAGE AMOUNT
SUB TOTAL
DEBT BF
DUE
DEBT CF
User payout
B132
01/01/2021
10
23
1.50
-14.50
0
-14.15
-14.15
0
B132
01/02/2021
30
23
4.50
2.50
-14.15
-10.6
-10.6
0
B132
01/03/2021
45
23
6.75
15.25
-10.6
0
0
4.65
B132
01/04/2021
35
23
5.25
6.75
0
0
0
6.75
Now on on product 2 below we can see this product hasn't recouped all of its "debt" and still an amount outstanding.
Product 2 -
Serial Number
Date
Vending
Monthly Rate
PERCENTAGE AMOUNT
SUB TOTAL
DEBT BF
DUE
DEBT CF
User Payout
C987
01/01/2021
10
23
1.50
-14.50
0
-14.15
-14.15
0
C987
01/02/2021
30
23
4.50
2.50
-14.15
-10.6
-10.6
0
C987
01/03/2021
10
23
1.50
-14.50
-10.6
0
-25.10
0.00
C987
01/04/2021
15
23
2.25
-10.25
-25.1
0
-35.35
0
What I am trying to workout is how can I look at every product in the dataset and only return the last DEBT CF amount if the user payout is below zero.
I have tried the LastValue function but this returns every products last DEBT CF, which isn't entirely true as above product 1 above wouldn't have any DEBT CF as it has broken-even with the User payout now above 0.
