Allocating payments and calculating age of dept

[PhilMeacham]

I have the below data and I'm trying to calculate the age of the debt. There's ony the one table and there's no matching of charge to payment so it's ok to assume that any payment is allocated to the oldest payment.

Customer ID	TransactionDate	Payment Type	Amount	Age
1	01/03/2020	Charge	£200.00	65.00
1	01/04/2020	Charge	£300.00	34.00
2	01/02/2020	Charge	£300.00	94.00
2	01/03/2020	Charge	£500.00	65.00
2	01/04/2020	Charge	£1,000.00	34.00
1	01/04/2020	Payment	-£200.00	34.00
1	01/04/2020	Payment	-£50.00	34.00
2	01/05/2020	Payment	-£400.00	4.00

From this data I'm wanting to be able to calculate the age of the debt. If there's a payment that is split over 2 charges then the oldest charge is allocated first and the rest rolls onto the next charge.

From the above table, I'd like to then have a table that looks as per below:

Customer	0 - 30	30 - 60	60 - 90	90+
1		£250.00
2		£1,000.00	£400.00

If anyone can help me with this, it would be very greatly apprecaited.

Thanks in advance!

[v-kelly-msft · Accepted solution]

Hi @PhilMeacham ,

Modify the measure as below:

Measure = 
var _table1=CALCULATETABLE(FILTER(ADDCOLUMNS('Table',"a",CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Customer ID]),'Table'[TransactionDate]<=MAX('Table'[TransactionDate])&&'Table'[Payment Type]="Charge")),"b",ABS(CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Customer ID]),'Table'[Payment Type]="Payment"))),
"c",CALCULATE(SUM('Table'[Amount]),FILTER(ALLEXCEPT('Table','Table'[Customer ID]),'Table'[TransactionDate]<=EARLIER('Table'[TransactionDate])&&'Table'[Payment Type]="Charge"))),[Payment Type] = "Charge"&&[b]<=[c]))
 
 
 
return
SUMX ( _table1, IF ( [c]-[b]<[Amount], [c] - [b],[Amount] ) )+0

And you will see:

For the updated .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PhilMeacham]

@v-kelly-msft thank you so much for your help with this.

However, I'm still struggling to get it to work but due to memory and it timing out.

I've been trying to get it to work by simplifying and working on the cumilative total.

The first step I've taken is to disregard if it's a payment or transaction and if the amount is > 0 ie it's a payment, then just set the date to 1st Jan 2000 (before the earliest transaction) this way the cummilative total as tie moves on will be deducted from the pot of payments that have made throughout time. I used a very simple query to create the date of the transaction below.

AgedDebt Date = IF('Transaction'[Amount]>0,DATE(2000,1,1),'Transaction'[TDate])

What I'm now struggling with is how I can now find the cumilitave total per age category by customer.

Does that make sense? I really appreciate your help to date!

[v-kelly-msft]

Hi @PhilMeacham ,

Go to query editor >add column>index column;

Then create 2 columns as below:

Age period = IF('Table'[Age]>=0&&'Table'[Age]<=30,"0 - 30",
IF('Table'[Age]>30 &&'Table'[Age]<=60,"30 - 60",
IF('Table'[Age]>60 &&'Table'[Age]<=90,"60 - 90",
IF('Table'[Age]>=90,"90+",BLANK()))))

Column 2 = 
var _jumpprevalue=MINX(FILTER('Table','Table'[Column]=EARLIER('Table'[Column])&&'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&'Table'[Payment Type]<>EARLIER('Table'[Payment Type])&&'Table'[Index]<EARLIER('Table'[Index])),'Table'[Index])

Return
_jumpprevalue

Finally create  a measure as below:

Measure 2 = 
var _jump= IF(MAX('Table'[Column 2])<>BLANK(),MINX(FILTER(ALL('Table'),'Table'[Customer ID]=MAX('Table'[Customer ID])&&'Table'[Age period]=MAX('Table'[Age period])&&'Table'[Payment Type]=MAX('Table'[Payment Type])&&'Table'[Index]<MAX('Table'[Index])),'Table'[Index]),0)
var _subtotal=SUMX(FILTER(ALL('Table'),'Table'[Age period]=MAX('Table'[Age period])&&'Table'[Customer ID]=MAX('Table'[Customer ID])),'Table'[Amount])
Return
IF(MAX('Table'[Column 2])=BLANK(),_subtotal,_subtotal-CALCULATE(MAX('Table'[Amount]),'Table'[Index]=_jump)
)

Create a matrix and you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PhilMeacham]

Hi @v-kelly-msft thanks for this. It's not quite right though as this would look like customer 1 had a total balance of £450 owing when they would only have £250 and it would be in the 30 - 60 date range.

The payments of £200 and £50 that are in the 30 - 60 range should be allocated to £200 that is in the 60 - 90 day range and then the remaining £50 allocated to the £300 in the 30 -60 day range resulting in just £250 owed in the 30 - 60 group.

Does that make sense?

I was wondering if it could be done by utilising a cumilative total based on the customer ID and then somehow use the max value in the age group?

[v-kelly-msft]

Hi @PhilMeacham ,

According to your supplementary instruction, for ID 1,30-60 is 250, 60-90 is 200..right?If so,where is the mistake in my result?

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[PhilMeacham]

Hi @v-kelly-msft thank you agin for you reply and apoogies for the confusion, I'll try and give it a little more context.

If you look at Customer 1, they have:

2 charges and 2 payments.

The oldest charge is £200 and 65 days old, the other charge is £300 and 34 days old.

The first payment of £200 should clear the oldest charge leaving the £300 that is 34 days old. Then the 2nd payment of £50 should then be put agains the new oldest charge ie the £300 that is 34 days old resulting in that debt being £250.

So the only debt that would be remaining for Customer 1 would be the £250 in the 30 - 60 category. There shouldn't be any debt that is 60 - 90 days.

Does that make a little more sense now?

Thank you so much for your help so far!