Forum Discussion

sokg's avatar
sokg
Icon for Solution Supplier rankSolution Supplier
9 years ago
Solved

Calculate open amount in column

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.

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      sokg
      Icon for Solution Supplier rankSolution Supplier
      9 years ago

      Hi Anonymous

       

      Thanks for your effort,It works.

       

      Thank you very much,