Amount Owed by Customers on Date

[Jbradad4x4]

Hello,

I have a transaction list with customer names and positive transactions for invoices raised against customers and negative transactions for payments made. 

DATE | CUSTOMER | TRANSACTION AMOUNT

2/1  |  Customer 3 | -$40

2/1  |  Customer 2 | -$30

2/1  |  Customer 3 | $60

1/1  |  Customer 2 | $30

1/1  |  Customer 1 | -$20

1/1  |  Customer 1 | $10

I need to know how much was owed by all customers on any given date. 

To get any customer's balance on a given date I can sum all transactions to that date. However some customers owe money which will result in a positive balance, and some are in credit which will result in a negative balance. 

To get the total owed, I cannot sum all customer transactions to date as it will give me both positive and negitave balances. I only want the positive customer balances (They Owe). 

I need a measure to complete the following steps:

1. Calculate each customers balance on each date (some 0, some + and some -). Each customers balance is calculated by summing all transactions (+ & -) to that date.

DATE | CUSTOMER | BALANCE OWED 

2/1  | Customer 1 | -$10

2/1  | Customer 2 | $0

2/1  | Customer 3 | $20

1/1  | Customer 1 | -$10

1/1  | Customer 2 | $30

1/1  | Customer 3 | $0

2. DESIRED RESULT: Calculate the total owed by all customers on each date (Sum all positive customer balances for each date, in this instance Customer 1 is filtered out on both dates as they have a negitave balance and therefore do not owe anything). 

DATE | TOTAL OWED BY CUSTOMERS

2/1   |   $20 

1/1   |   $30

I want to create a visual to show the total amount owed by customers over time. 

Excel file with example data:

https://www.icloud.com/iclouddrive/092z8z19He0_JEFBskbd-ljOA#Sample_GL_Data 

Example pbix:

https://www.icloud.com/iclouddrive/06cgtwF2ttEck1uOsdNiFU-7w#Sample_GL_Data 

I have dax from my attempts to solve this but feel it may actually confuse the description of what I am trying to do. It is in the pbix anyhow. Second attempt at solving this and I have tried to explain it as clearly as po

[Jbradad4x4]

Sorry, actualy that is not working. 

When I graph Customer Due over time, it shows the total of customers with positive values with transactions on that day, not the total of all customers with positive balances. 

I used the method of two measures - a sum and a sumx but edited them to the following and it is working now. 

Thanks for your help.

Cumm TRANSACTION AMOUNT=

VAR LastVisibleDate =
MAX ( 'Date'[Date] )

VAR FirstVisibleDate =
MIN ( 'Date'[Date] )
VAR LastDateWithTrans =
CALCULATE (
MAX ( Table[TransactionDate] ),
REMOVEFILTERS ('Date'[Date])
)
VAR Result =
IF (
FirstVisibleDate <= LastDateWithTrans,
CALCULATE (
sum(Table[TRANSACTION AMOUNT]),
filter(all('Date'[Date]),'Date'[Date] <= LastVisibleDate),


)
)
RETURN
Result

Customer Due =

Calculate(

Sumx(filter(Values(Table[CUSTOMER]), [Cumm TRANSACTION AMOUNT]> 0), [Cumm TRANSACTION AMOUNT]),
filter(ALL('Date'[Date]),'Date'[Date]<= MAX ( 'Date'[Date] ))

[amitchandak · Accepted solution]

@Jbradad4x4 , With help from date table create running balance

Cumm TRANSACTION AMOUNT = calculate(Sum(Table[TRANSACTION AMOUNT]), filter(all('Date'), 'Date'[Date]<= Max('Date'[Date])))

Then create a measure like

Customer due
= Sumx(filter(Values(Table[CUSTOMER]), [Cumm TRANSACTION AMOUNT]> 0),[Cumm TRANSACTION AMOUNT])

[Jbradad4x4]

This worked thank you, so simple!!!