Forum Discussion
Remaining value count
Hi all-
I have contract values for the same contracts. The contract value shows the same value over and over as it is the same contract for each invoice. I need to create a column that takes the invoice amount minus the contract amount but that will put the nex value in the next row. Baiscally, I want to show the paid off amount getting smaller and smaller as each invoice is paid to the contact, eveutally beign 0$.
| Contract ID | Contract amount | Invoice amount | Invoice amount remaining | What I want to show(remaining) |
| 1 | 100 | 25 | 75 | 75 |
| 1 | 100 | 25 | 75 | 50 |
| 1 | 100 | 25 | 75 | 25 |
| 1 | 100 | 25 | 75 | 0 |
Hi edavis248 ,
Do you mean that when this column "What I want to show (remaining)" is negative, it also outputs 0? If yes, we can create a column.
What I want to show(remaining) = var a= CALCULATE(SUM('BGE_INVOICES'[Contract amount]),FILTER('BGE_INVOICES','BGE_INVOICES'[Index]=1)) var b=SUMX(FILTER('BGE_INVOICES','BGE_INVOICES'[Contract ID]=EARLIER('BGE_INVOICES'[Contract ID])&&'BGE_INVOICES'[Index]<=EARLIER('BGE_INVOICES'[Index])),'BGE_INVOICES'[Invoice amount]) return IF((a-b)>=0,(a-b),0)I don't think I'm very clear about your new needs, my suggestion is that you mark the answer to the current post as a solution and recreate the post to describe the other needs in detail, thank you for your time.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi edavis248 ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2)Click "Transform Data" to enter the Power Query editor and add an index column.
(3) We can create a calculated column.
What I want to show(remaining) = var a= CALCULATE(SUM('Table'[Contract amount]),FILTER('Table','Table'[Index]=1)) var b=SUMX(FILTER('Table','Table'[Contract ID]=EARLIER('Table'[Contract ID])&&'Table'[Index]<=EARLIER('Table'[Index])),'Table'[Invoice amount]) return a-b(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks Neeko I think this is what I need. However is there a reason my EARLIER function wont pull in the column?
I actually added at a column not the measure so it worked technically. But it is showing some mass negative values. I have quiet a bit more contracts than my example. I am getting this result:
Appreciate any help I can work to get a workign file without sentitive info.
In the below I would expect the "Column" to show wha tthe inv amt remaining column does but trickle down to $0. currently I have the contract total - invoice amount of each invoice but was that value for 10/15/2021 to start the next line then $140,491 - 3,817(invoice amount) then the next line would be $136,674. Appreciate any help, I have been working on this for weeks.
