Forum Discussion

edavis248's avatar
edavis248
Icon for Helper I rankHelper I
3 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
edavis248
Icon for Helper I rankHelper I
3 years ago

Thanks Neeko I think this is what I need. However is there a reason my EARLIER function wont pull in the column? 

 

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
    edavis248
    Icon for Helper I rankHelper I
    3 years ago

    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.