cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Amount Owed by Customers on Date

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:

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

1 ACCEPTED SOLUTION
Super User

@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])

3 REPLIES 3
Frequent Visitor

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] ))

Super User

@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])

Frequent Visitor

This worked thank you, so simple!!!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors