Forum Discussion

ayana's avatar
ayana
Frequent Visitor
2 years ago
Solved

Calculated column to apply values from a different column

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's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    which business question are you trying to answer with this?