Calculated column to apply values from a different column

[ayana]

I'm working with debt collection data and I have a problem I hope someone can help resolve. Scenario: When there are several missed payments and a collection is made, it is applied to the oldest missed payment(s). For instance, if there are missed payments in Jan, Feb, Mar and April, ($500 each month, cumulative $2000) and a payment is made in April (e.g.$1000), that payment is applied to Jan ($500) and Feb ($500). If the total missedpayment is made ($2000), it is applied to Jan, Feb, March and April and the account becomes current. The challenge is that the source data does not have this information and I would like to create a DAX command to that applies the collected amount to the oldest missedpayments. Does anyone know if this is possible and if so, how? A sample table showing the expected results is included. For this, I'm only working with a fact table and a date table.

Thanks in advance for your insights!

. 

[Anonymous · Accepted solution]

Hi @ayana 

Please try creating “CreditedAmount” column with the following DAX:

CreditAmount = 
VAR CurrentMissedPaymentID = 'DataTable'[MissedPaymentID]

VAR CumulativeCollectedAmount =
CALCULATE(
    SUM('DataTable'[CollectedAmount]),
    FILTER(
        ALL('DataTable'),
        'DataTable'[Month] <= EARLIER('DataTable'[Month])
    )
)

VAR PreviousCumulativeMissedPmts =
CALCULATE(
    MAX('DataTable'[CumulativeMissedPmts]),
    FILTER(
        'DataTable',
        'DataTable'[MissedPaymentID] = CurrentMissedPaymentID - 1
    )
)

RETURN
IF(
    CumulativeCollectedAmount - 'DataTable'[CumulativeMissedPmts] >= 0,
    'DataTable'[MsdPmtAmount],
    MAX(0, CumulativeCollectedAmount - PreviousCumulativeMissedPmts)
)

Here is my test result, i hope this can meet your requirement.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

which business question are you trying to answer with this?