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

[Anonymous]

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?

[Anonymous]

@Anonymous could you please try the below DAX code

[johnt75]

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]
)

[Anonymous]

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: 

[johnt75]

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

[Anonymous]

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? 

[johnt75]

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

[Anonymous]

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?

[johnt75 · Accepted solution]

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

[Anonymous]

That works, thanks a lot for your help!

[daXtreme]

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.