track status change month to month

[Anonymous]

Good morning. 

I'm left with a bit of a problem. I'm trying to track status changes each month in the delinquency status of loans. The work that i'm trying to build off of was created in mosaic by someone else and passed off to me to create in PBI.  

    

I have fields for snapshot dates that are always at the end of the month, the status of the loan, a binary field for if its the current month, and a count based on how far back the snapshot date is from the current month. I honestly have no idea how to begin to write the Dax for this and any help would be greatly appreciated. 

snapshot_date	loan_number	delinquency_status	months_back	is_current_month
7/31/2019	1111111221	current	0	1
6/30/2019	1111111221	30 days	1	0
5/31/2019	1111111221	60 days	2	0

[v-cherch-msft]

Hi @Anonymous 

I cannot fully understand it.Please explain more about your expected output.Please follow the How to Get Your Question Answered Quickly to post your simplified assumed data and expected output.It would be better if you can upload the .pbix file to OneDrive and post the link here.

Regards,

[Anonymous]

I can't post the dataset. 

I'm creating a column for each of the default groupings. Theoretically, each of these columns will compare the loan number to the one in the row above it. If the loan number changes, it marks it. If it doesn't change, it then should evaluate if Current row's status is different from the status of the row above it. I'm trying to use earlier.... but i haven't been able to crack it yet. I get an error that EARLIER refers to an earlier row context which doesn't exist. This is my code at the moment. 

30_to_C =

IF (

EARLIER ( rlms_monthend[Loan_number] ) <> rlms_monthend[loan_number],

"New Loan",

IF (

EARLIER ( rlms_monthend[default_group_mba] ) = "30 days" & rlms_monthend[default_group_mba] = "curent",

"30 - C",

"-"

)

)

[Anonymous · Accepted solution]

Found a working solution. 

previous month value =

CALCULATE (

FIRSTNONBLANK ( 'rlms_monthend'[default_group_mba], 1 ),

FILTER (

'rlms_monthend',

EARLIER ( 'rlms_monthend'[loan_number] ) = 'rlms_monthend'[loan_number]

&& DATEDIFF (

EARLIER ( 'rlms_monthend'[snapshot_date] ),

'rlms_monthend'[snapshot_date],

MONTH

) = -1

)

)