Calculate open amount in column

[sokg]

Hello to all,

I need help with a calculated column.

I have 2 tables, one is SalesFactTbl and looks like the pic below

And the second is CustBalanceTbl and looks like the pic below

I want to create a calculated column in my SalesFactTbl to show the open amount for each transaction, just like i show in the pic below.

where balance is split  across transactions from the newest to oldest

Need help with this calculation.

[Anonymous · Accepted solution]

Hi @sokg,

Based on your description, you want to get the dynamic order distribution amount, right?

If this is a case, you can take a look at below formula if it suitable for your requirement.

Logic: calculate the running total amount and remaining amount, if remain amount greater than current amount, show the current amount, if it less than current amount, show the remaining amount.

Open Amount = 
var balance = LOOKUPVALUE(CustBalance[Balance],CustBalance[Cust ID],SalesFact[Cust ID])
var runningTotal = SUMX(FILTER(ALL(SalesFact),[TRN Date]>EARLIER(SalesFact[TRN Date])&&[Cust ID]=EARLIER(SalesFact[Cust ID])),[Sales Amount]) 
var remaining = balance-runningTotal
return
if(remaining>0,if(remaining>[Sales Amount],[Sales Amount],remaining),0) 

Regards,

Xiaoxin Sheng

[sokg]

Hi @Anonymous

Thanks for your effort,It works.

Thank you very much,