Update AR Aging with Amounts Received

[svelamuri]

I have an Accounts Receivable aging report that I want to update as we receive funds. The amounts received are not consistent with the exact dollar amount of the dues, so I have to subtract the amounts received from the earliest first.

For example, from the report below, if we receive $10 from Bank of America Amex, it should be subtracted from the earliest (30 days) first and then the remaining from the recent (Current due).

So after updating, there should be nothing under 30 days due. The current due would be $7.64.

I couldn't figure out a way to start working on this. Any help would be greatly appreciated, thank you!

This is how the aging report looks like: 

This is how the data looks: 

The data of amounts received looks like this: 

The relationship between the two table is many to many:

[johnbasha33]

Hi @svelamuri 

try below measure

Current Due =
VAR RemainingDues =
ADDCOLUMNS (
FILTER (
AgingReport,
[Amount Due] > [Amount Received]
),
"RemainingDues", [Amount Due] - [Amount Received]
)
RETURN
SUMX ( RemainingDues, [RemainingDues] )

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

[svelamuri]

Hi @johnbasha33 

Thank you for your response.

However, this doesn't work in my case because Amount Due and Amount received are not in the same table or the same format. Please refer to the screenshots - let me know if you need more information. 

Also, I need to be subtracting the amount received from amount due in the descending order of the aging category - so subtract from 60 days first (if anything in 60 days exists), then any remaining amount received from 30 days and lastly from current due. 

Thank you!