Forum Discussion
Helper IRemaining value count
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.
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.
edavis248Thanks Neeko I think this is what I need. However is there a reason my EARLIER function wont pull in the column?
- edavis248
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.
Hi edavis248 ,
Please click "New Column" in the "Modeling" pane and copy the following dax.
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 a-bBest Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
- edavis248
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.