Total Debtors Amount Trend over Time

[Jbradad4x4]

I am looking to use a transaction table to show the total amount owed by customers on a given date so I can show a line graph over time. The data has the transaction amounts - TGROSS (positive for invoices and negitave for payments from customers), customer codes - TCODE and transaction dates - TDATE.

I can total the customer balances on any date but the problem I have is that I only want to add the customers to the total if they owe on that date. If a customers balance is negitave then I want to ignore them as I only want debtors total. 

The Dax I have so far is:

$ Customer Balance =

VAR Lastvisibledate =
max('Date'[Date])

VAR Result =

CALCULATE (
sum('Sample GL Data'[TGROSS]),
ALLEXCEPT('Sample GL Data','Sample GL Data'[TCODE]),
'Sample GL Data'[TGLCODE] = 1030,
'Date'[Date] <= Lastvisibledate ,
USERELATIONSHIP('Date'[Date],'Sample GL Data'[TDATE])
)
RETURN
Result

However this shows the total position of all customers both debtors and those in credit. I can't figure out how to filter negitave balances after conducting the initial SUM. 

I have other meausres using the GST DATE so have two date relationships and want to use TDATE for this calculation hence USERELATIONSHIP. 

Link to pbix

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

Any help greatly appreciated. 

[Jbradad4x4]

@dhruvinushah Thanks for the reply. 

I dont think I can filter TGROSS like that as it will then exclude all invoices (positive) and only SUM the customer payments made (negative). This will give a large negative number. I need to sum all positive and negative TGROSS for each customer, this will return some customers with positive totals (debtors) and some with negative totals (in credit). I need to then filter out the customers with negative totals and sum the customers with positive totals to give total debtors amount. 

I hope that makes sense.  

[dhruvinushah]

Hi @Jbradad4x4 , 
You can try to add another filter to your existing measure for the TGROSS or the TransactionAmount Field to filter out the transactions pertaining to payments.

$ Customer Balance =

 

VAR Lastvisibledate =
max('Date'[Date])


VAR Result =

CALCULATE (
sum('Sample GL Data'[TGROSS]),
ALLEXCEPT('Sample GL Data','Sample GL Data'[TCODE]),
'Sample GL Data'[TGLCODE] = 1030,
'Date'[Date] <= Lastvisibledate ,
'Sample GL Data' [TGROSS] >0,
USERELATIONSHIP('Date'[Date],'Sample GL Data'[TDATE])
)
RETURN
Result

Hope this helps, please mark my answer as solution or give a thumbs up if it helped. Thanks!

[Jbradad4x4]

Hi Ashish,

Excel file here:

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

I calculated the balance for each customer for each date then only summed the positive balances to get the total debtors amount. Then graphed it over 12 weeks which is the desired result. 

Thanks. 

[Ashish_Mathur]

Hi,

In cell M4, you have shown the result as 1203.75 - that is wrong.  The balance on 05/05/2022 should be 203 (ignoring the -203 balance on the same date).

[Jbradad4x4]

Hi Ashish,

I'm sorry for the confusion. I dont want to ignore negative values in column E. What I want to do is add all values (+ & -) per customer, to date.

This will effectively create a new table for every historical date - a list of all customers and their total sum (of - and + values from Column E) up to and including that date. Some customers will have a 0 total sum, some will + and some -. This is shown on columns H-J.

I want a total of the customers with positive values for each date (how much was the company owed on that date). Effectively this will create a thrid table, the total of customers with a positive value (amount owed) on each historical date. This is shown on columns L-M.

Since the total owed on 20/4/22 was 1203.75 - on 5/5/22 the +203 and -203 = 0 so no net change and 1203.75 is still the amount owed. 

[Ashish_Mathur]

I just cannot understand your MS Excel file.  Sorry I cannot help.  May be someone else will help you.

[Ashish_Mathur]

Hi,

Share a small dataset (which can be pasted in MS Excel) and show the expected result.