Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |