Measure for customer payments over time towards current balance

[AA622]

I am creating an AR report where I need to show payments against a customer account, customer invoice#, and the original amount

The issue Im having is the different payments go toward the original amount of 10,000

In the example above the customer made an initial payment of 5,000 lowering the balance to 5,000.

Then they made a payment of 1000 which shouldve lowered it to 4,000 but instead it went toward the intial balance of 10,000 making it 9,000

How do I create a measure that will take into account the new current balance? Any help would be appreciated

[amitchandak]

@AA622 , You need to have an inventory Approach, when using cumulative amounts. with help from date table

CALCULATE(SUM(Table[Amount Due]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Amount Paid]),filter(date,date[date] <=maxx(date,date[date])))

We sometimes use intial qty there. But I dobut that will apply here

example

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

Inventory / OnHand
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table[Intial Inventory]),all('Date')) + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

refer

Power BI Inventory On Hand
Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[AA622]

Hey thanks I tried with the first measure but I still get the same results. This is what I made

CurrentBal3 =
CALCULATE(
SUM(Table[OrigAmt3]),
FILTER('Table',Table[Invoice Date] <= MAXX('Table',Table[Invoice Date]))) -
CALCULATE(
SUM(Table[PayymentAmt2]),
FILTER('Table',Table[Invoice Date] <= MAXX('Table',Table[Invoice Date])))

Should I be using a different parameter for the date portion? I do have a calendar/date table FYI

Keep in mind that this is per customer account who has an invoice(bill) which is CSM001400. Original amount is 10,000 and they are making payments over different dates in different increments. In this case its 5,000 and 1,000. Bal should be 4,000