Getting Status from row of previous month

[bonjourposte]

Hi all, I have a big list of loans, with one line per month in the loan term, denoted by the column Accounting Date.  Someone helped me get the "Previous Maturity Date" from the previous month's line, but I can't seem to translate the same code to "Previous Loan Status".  

Here is what the table looks like (Loan Status columns in pink).  I want to create the column "PrevLoanStatus":

Here is the DAX the person gave me for Previous Maturity Date (it worked):

When I tried converting that to Previous Loan Status, but it gives an entire column of the very first status when setting up a loan that I thought I had filtered out in Power Query.  

PreviousLoanStatus =

var _cLOAN = LOANHISTSorted[LOAN]

var _AcctDt = LOANHISTSorted[ACCOUNTING_DATE]

var Loansatus = LOANHISTSorted[LOAN_STATUS]

var PrevLoanStatus =

CALCULATE(

    MAX(

        LOANHIST[LOAN_STATUS]),

        FILTER(ALLSELECTED(LOANHISTSorted),

        LOANHISTSorted[LOAN] = _cLOAN

        && LOANHISTSorted[ACCOUNTING_DATE] < _AcctDt))

RETURN

PrevLoanStatus

___________________________________________________________________________

How can I get it to just return a column of the "Loan Status" from the previous Accounting Date for the loan?  

Here is my dummy data from that table above:

Loan#	Accounting Date	Loan Term (mos)	Loan Status	PrevLoanStatus	Mat_Date	Previous Month's Mat_Date
101	30-Sep-22	3	REPAYMENT	REPAYMENT	30-Sep-22	30-Sep-22
101	31-Oct-22	3	REPAYMENT	REPAYMENT	30-Nov-22	30-Sep-22
101	30-Nov-22	3	ARREARS<90	REPAYMENT	30-Nov-22	30-Nov-22
202	31-Jan-23	4	ADV_PREIAD	ADV_PREIAD	31-Mar-23	31-Mar-23
202	28-Feb-23	4	REPAYMENT	ADV_PREIAD	31-Mar-23	31-Mar-23
202	31-Mar-23	4	REPAYMENT	REPAYMENT	30-Apr-27	31-Mar-23
202	30-Apr-23	4	REPAYMENT	REPAYMENT	30-Apr-27	30-Apr-27
303	28-Feb-23	2	COMMITMENT	COMMITMENT	1-Mar-23	1-Mar-23
303	31-Mar-23	2	ADV_POSTIAD	COMMITMENT	1-Mar-23	1-Mar-23
404	31-Aug-23	3	COMMITMENT	COMMITMENT	30-Sep-23	30-Sep-23
404	30-Sep-23	3	REPAYMENT	COMMITMENT	30-Sep-23	30-Sep-23
404	31-Oct-23	3	REPAYMENT	REPAYMENT	1-Dec-24	30-Sep-23

Thank-you so much!

EDIT: link to my original question: https://community.fabric.microsoft.com/t5/Desktop/Get-data-from-previous-month-previous-row-but-grou...

[Ashish_Mathur]

Hi,

Show the expected result very clearly.

[bonjourposte]

Sorry I didn't realize it was unclear.  This is the column I'm wanting to achieve.  

User SevenHills graciously provided this DAX, but it only partially works.  It works for the first and third loan statuses per loan.  

[Ashish_Mathur]

Hi,

Write these calculated column formulas

Previous maturity date = COALESCE(CALCULATE(MAX(Data[Mat_Date]),FILTER(Data,Data[Loan#]=EARLIER(Data[Loan#])&&Data[Mat_Date]<EARLIER(Data[Mat_Date]))),Data[Mat_Date])

Prev loan status = COALESCE(CALCULATE(MAX(Data[Loan Status]),FILTER(Data,Data[Loan#]=EARLIER(Data[Loan#])&&Data[Accounting Date]<EARLIER(Data[Accounting Date]))),Data[Loan Status])

Hope this helps.

[bonjourposte]

Thanks so much, @Ashish_Mathur, I'm definitely getting warmer.  Your formula worked in the way that SevenHills' didn't, but there are still some loan numbers that yours didn't work for: the ones where there were three loan statuses- it just took the first and the third.  

Here is one where yours WORKED and theirs didn't: 

Your DAX

And here is one where theirs captured all three statuses, but don't capture the previous line like yours did:

What I was using for theirs (I had to modify it a bit to get the above):

Somehow the best of both formulas need to be combined.

I truly appreciate your time and effort, I am not good with CALCULATE, especially ALLSELECTED!

[Ashish_Mathur]

You are welcome.  I cannot help with just an image.  As you can see in my previous post, i got exactly the result that you wanted.  If you did not describe the question properly, now is the time to do so showing all possible scenarios and the expected result.  Also, share data in a format that can be pasted in an MS Excel file.  Sharing a picture is useless.

[bonjourposte]

Thanks, @Ashish_Mathur.  I'm sending one that works and two that don't work.  I'm still trying to understand why the sample data gave no errors.  Could it be that the other columns in the table provide additional context?  

https://drive.google.com/drive/folders/1LOYfSdoU05shvP_U3B1fPcGgXk53XGDg?usp=sharing

____________________________________

Here is the DAX you provided:

PreviousLoanStatusAshish =

COALESCE(

    CALCULATE(

        MAX(

            LOANHISTSorted[LOAN_STATUS]),

            FILTER(LOANHISTSorted,LOANHISTSorted[LOAN] = EARLIER(LOANHISTSorted[LOAN])

            &&

            LOANHISTSorted[ACCOUNTING_DATE] < EARLIER(LOANHISTSorted[ACCOUNTING_DATE]))),

            LOANHISTSorted[LOAN_STATUS])

______________________________________

Here is SevenHills' Dax:

PreviousLoanStatus7Hills =

var _cal =

CALCULATE(

    MAX(

        LOANHISTSorted[LOAN_STATUS]),

        LOANHISTSorted[ACCOUNTING_DATE] < (LOANHISTSorted[ACCOUNTING_DATE]),

        FILTER(ALLSELECTED(LOANHISTSorted),

        LOANHISTSorted[LOAN] = EARLIER(LOANHISTSorted[LOAN])

        &&

        LOANHISTSorted[ACCOUNTING_DATE] = EARLIER(LOANHISTSorted[ACCOUNTING_DATE])))

    RETURN

    COALESCE(_cal, LOANHISTSorted[LOAN_STATUS])

_________________________________________

I feel like it should be a combination of the two somehow?

Or could it be that the DAX is putting the Loan_Statuses in alphabetical order or something?  Here is a list of possible Statuses:

[Ashish_Mathur]

I am confused.  Just share the download link of 1 Excel file with a column of expected results.  Simple.

[bonjourposte]

Sorry I tested the link twice.  not sure what's wrong.  I'll just repost the question for someone else to answer.  Thanks for all the attention you've put toward my question.

[v-xingshen-msft]

Hi @bonjourposte ,
Your code is working fine in my environment, you can check my screenshot to see if it meets your needs, I hope it helps!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[bonjourposte]

Hi @v-xingshen-msft , yeah for some reason I'm not getting the same results.  This is what I get with that formula: 

What I used for that last column:

PreviousLoanStatusXing =

var _cLOAN = MAX(LOANHISTSorted[LOAN])

var _AcctDt = MAX(LOANHISTSorted[ACCOUNTING_DATE])

var _Loanstatus = MAX(LOANHISTSorted[LOAN_STATUS])

var _PreLoanStatus =

CALCULATE(

    MAX(

        LOANHISTSorted[LOAN_STATUS]),

        FILTER(ALLSELECTED(LOANHISTSorted),

        LOANHISTSorted[LOAN] = _cLOAN && LOANHISTSorted[ACCOUNTING_DATE] < _AcctDt)

)

RETURN

_PreLoanStatus

[lbendlin]

If "previous row"  always means "prior month"  then you are better off adding a calendar table to your data model and using the default DATEADD(xx,-1,MONTH)  time intelligence function.