topic Re: Problem with the column that calculates the previous value of a column in DAX Commands and Tips

Problem with the column that calculates the previous value of a column

Anonymous — Wed, 23 Mar 2022 10:32:22 GMT

Hi,

I have table "prod_snapshot", that is snapshot data of customer records. The table has a calculated column "default tagi", that tags default with an IF-statement, returning either 1 or 0 based on whether the customer is defaulted or not. I need a column that returns the previous value of a default tagi column.

I have used syntax:

previous default = CALCULATE(FIRSTNONBLANK('prod snapshot'[default tagi], 1 ), filter('prod snapshot', 'prod snapshot'[loan_id] = earlier( 'prod snapshot'[loan_id]) && 'prod snapshot'[Year Month order] = earlier('prod snapshot'[Year Month order]))).

The Year Month Order is a column that gives order number for each month in an ascending order.
The problem is that for some customer records (=loan_id) it works and for some it gives the current value of default tagi.

When Year Month Order = 98 previous default returns 1, that is not the previous value of default tagi.

For this loan_id, it returns the correct value:

Could anyone help me with this?

Re: Problem with the column that calculates the previous value of a column

daXtreme — Wed, 23 Mar 2022 12:20:09 GMT

define table Loans = SELECTCOLUMNS( { (1, 1, 0), (1, 2, 0), (1, 3, 1), ---------- (2, 1, 0), (2, 2, 1), (2, 3, 1) }, "loan_id", [Value1], "ym_order", [Value2], "def_tagi", [Value3] ) EVALUATE ADDCOLUMNS( Loans, "prev_def", var curr_loan_id = Loans[loan_id] var curr_ym_order = Loans[ym_order] var prev_def_tagi = MAXX( filter( Loans, Loans[loan_id] = curr_loan_id && Loans[ym_order] = curr_ym_order - 1 ), Loans[def_tagi] ) return COALESCE( prev_def_tagi, 0 ) )

Run this in DAX Studio. The definition of "prev_def" is what you need.

Re: Problem with the column that calculates the previous value of a column

johnt75 — Wed, 23 Mar 2022 12:24:30 GMT

You can use TOPN

previous default = var currentLoan = 'Table'[loan_id] var currentYearMonth = 'Table'[Year Month order] return SELECTCOLUMNS( TOPN(1, CALCULATETABLE( REMOVEFILTERS('Table'), 'Table'[loan_id] = currentLoan && 'Table'[Year Month order] < currentYearMonth ), 'Table'[Year Month order], DESC), "@value", 'Table'[default tagi] )

Re: Problem with the column that calculates the previous value of a column

Anonymous — Wed, 23 Mar 2022 13:35:40 GMT

Thanks for your reply! Unfortunately, it doesn't work. I replaced REMOVEFILTERS() with ALL(), as apparently REMOVEFILTERS cannot be used with calculatetable expression, but still throws this error message:

Re: Problem with the column that calculates the previous value of a column

johnt75 — Wed, 23 Mar 2022 13:40:02 GMT

Do you have multiple entries in a month for a loan ? If so you may need to add a unique index column using Power Query and you can then add that index column as another sort on the TOPN, that should only return 1 value per loan then

Re: Problem with the column that calculates the previous value of a column

Anonymous — Wed, 23 Mar 2022 14:08:30 GMT

Yes I have, but I want that the previous default column considerers only those values from default tagi -column that are from different month.

I made Index column, that gives different index for every report date:

Index = RANKX (FILTER ('prod snapshot', EARLIER ('prod snapshot'[loan_id]) = 'prod snapshot'[loan_id]),[report_date],, ASC)

Then I tried to plug this into TOPN- function:

previous default =

var currentLoan = 'prod snapshot'[loan_id]

var currentYearMonth = 'prod snapshot'[Year Month order]

return SELECTCOLUMNS( TOPN(1, CALCULATETABLE( ALL('prod snapshot'),

'prod snapshot'[loan_id] = currentLoan && 'prod snapshot'[Year Month order] < currentYearMonth ),

'prod snapshot'[Index], DESC),

"@value", 'prod snapshot'[default tagi]

)

It still gives the same error. I am pretty new to power bi, so I am not quite sure about how that function operates.

Could you please show me how it should be included and if the Index column is correctly defined?

Re: Problem with the column that calculates the previous value of a column

johnt75 — Wed, 23 Mar 2022 14:20:21 GMT

The problem with your index column is that it will give the same result to different entries with the same date, and so you have the same problem as before.

I would use Power Query to add the column. Sort by the date column then add an index column, doesn't matter whether it starts from 0 or 1.

That will guarantee that there will be a unique index and you can use that in TOPN

Re: Problem with the column that calculates the previous value of a column

Anonymous — Wed, 23 Mar 2022 14:42:58 GMT

Anonymous could you please try the below DAX code

Re: Problem with the column that calculates the previous value of a column

Anonymous — Wed, 23 Mar 2022 14:46:44 GMT

Thanks, the index column was easy to do in Power Query as you told.

The TOPN-function works now, but now previous default column seems to return"1" for every report date:

Was the TOPN -function correct as I posted it or should the Index column be included in a different way?

Re: Problem with the column that calculates the previous value of a column

johnt75 — Wed, 23 Mar 2022 15:06:22 GMT

Replace the CALCULATETABLE with FILTER, that works in the sample I've mocked up.

Re: Problem with the column that calculates the previous value of a column

Anonymous — Mon, 28 Mar 2022 09:47:09 GMT

That works, thanks a lot for your help!