topic Re: Calculated column to apply values from a different column in DAX Commands and Tips

Calculated column to apply values from a different column

ayana — Wed, 21 Aug 2024 15:10:56 GMT

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!

Re: Calculated column to apply values from a different column

lbendlin — Thu, 22 Aug 2024 00:09:18 GMT

which business question are you trying to answer with this?

Re: Calculated column to apply values from a different column

Anonymous — Wed, 28 Aug 2024 01:32:27 GMT

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.