Get data from previous month (previous row), but grouped by loan #

[bonjourposte]

I've got a table that contains several lines for each loan.  Each row is one month of that loan's term, and at some point in the loan's life, the maturity date might change (it might get an extension or renewal).  I'm creating a column that lists the PREVIOUS MONTH's maturity date (because I want to see what the maturity date was last month in order to compare the CURRENT vs. the PREVIOUS).  

A few of you have helped me and told me to use DATEADD or PARALLELPERIOD, and I could be doing the DAX wrong, but neither of them work because my dates aren't contiguous (they are just the last day of every month).  I've tried referencing the Date calendar, but that just returns blanks.  How do I tell DAX that I just want to grab the maturity date from the previous row that deals with that Loan?

here is some sample data.  

Loan#	Accounting Date	Loan Term (mos)	Current_Mat_Date	Previous Month's Mat_Date
101	30-Sep-22	3	30-Sep-22
101	31-Oct-22	3	30-Nov-22	30-Sep-22
101	30-Nov-22	3	30-Nov-22	30-Nov-22
202	31-Jan-23	4	31-Mar-23
202	28-Feb-23	4	31-Mar-23	31-Mar-23
202	31-Mar-23	4	30-Apr-27	31-Mar-23
202	30-Apr-23	4	30-Apr-27	30-Apr-27
303	28-Feb-23	2	1-Mar-23
303	31-Mar-23	2	1-Mar-23	1-Mar-23
404	31-Aug-23	3	30-Sep-23
404	30-Sep-23	3	30-Sep-23	30-Sep-23
404	31-Oct-23	3	1-Dec-24	30-Sep-23

(I don't know what to do about the first month of every loan.  For now I've left it blank, but I might just fill it with the original maturity date, I don't think that harms anything.  I'm just trying to detect those few months when the Previous Month's Mat_Date differs from the current Mat_Date.)

Thanks for your help!

EDIT: screenshot of my table in case it's clearer: 

[sevenhills · Accepted solution]

Try this:

Measure Prev Month Mat Date = 
var _cLoan = SELECTEDVALUE('Table'[Loan#])
var _cTerm = SELECTEDVALUE('Table'[Loan Term (mos)])
var _cAccDt = SELECTEDVALUE('Table'[Accounting Date])
var _cMatDt = SELECTEDVALUE('Table'[Current_Mat_Date])
var _cal = CALCULATE( max('Table'[Current_Mat_Date]), 
                  filter(ALLSELECTED('Table'), 
                         'Table'[Loan#] = _cLoan 
                      && 'Table'[Loan Term (mos)] = _cTerm 
                      && 'Table'[Accounting Date] < _cAccDt))
return COALESCE(_cal, _cMatDt)

I used COALESE as you said you want to current row maturity date. 

output:

Hope this helps!

Note: If you want the value to be blank, then replace coalesce line with "return _cal" and you have to select "Show items with no data" in the table visual.

[sevenhills · Accepted solution]

Try this:

Measure Prev Month Mat Date = 
var _cLoan = SELECTEDVALUE('Table'[Loan#])
var _cTerm = SELECTEDVALUE('Table'[Loan Term (mos)])
var _cAccDt = SELECTEDVALUE('Table'[Accounting Date])
var _cMatDt = SELECTEDVALUE('Table'[Current_Mat_Date])
var _cal = CALCULATE( max('Table'[Current_Mat_Date]), 
                  filter(ALLSELECTED('Table'), 
                         'Table'[Loan#] = _cLoan 
                      && 'Table'[Loan Term (mos)] = _cTerm 
                      && 'Table'[Accounting Date] < _cAccDt))
return COALESCE(_cal, _cMatDt)

I used COALESE as you said you want to current row maturity date. 

output:

Hope this helps!

Note: If you want the value to be blank, then replace coalesce line with "return _cal" and you have to select "Show items with no data" in the table visual.

[bonjourposte]

Thank-you!  The measure works.  Much appreciated.

If you knew how to turn this into a column, it would be great too.

EDIT: I got it to work as a column- I just removed all the SELECTEDVALUE's out of the variables.  Awesome, thanks again.

[sevenhills · Accepted solution]

My bad now that I read one more time, you were asking for column syntax. you mentioned that you got the solution. glad to hear it.

Posting here for future: DAX column syntax:

Column Prev Month Mat Date - DAX = 
var _cal = CALCULATE( max('Table'[Current_Mat_Date]), 
                  filter(ALLSELECTED('Table'), 
                         'Table'[Loan#] = EARLIER('Table'[Loan#])
                      && 'Table'[Loan Term (mos)] = EARLIER('Table'[Loan Term (mos)]) 
                      && 'Table'[Accounting Date] < EARLIER('Table'[Accounting Date])))
return COALESCE(_cal, 'Table'[Current_Mat_Date])

Output:

Personally, I prefer the Power Query to do the same.

[bonjourposte]

Thanks, @sevenhills , so much appreciated.  however, I used your formula for the Previous Maturity Date and it works fine, but it doesn't work for Previous Loan Status.  Do you know why?  Here's the link to the results for each formula with a loan that has three status changes.  

https://docs.google.com/spreadsheets/d/11DmuEieNSoWMaz9fnHHgB7tOAr5OUl04TfdnVtks_ws/edit?usp=sharing

I'm trying to get my Previous Loan_Status column to read the Loan Status of the month prior.  You can see it works with Mat_Date (blue), but not with Loan_Status (yellow) and I can't figure out why for the life of me.

[sevenhills]

I looked at your file provided in google docs.  I think the DAX you are doing is wrong. Before getting into the DAX, let us define the output. What is previous loan status? 

Are you looking for the same row value of loan status that is on the "Column Prev Month Mat Date - DAX"?

Thanks