Month against previous months on a rolling basis

[ramesh_neel]

My very first alteryx post so please do let me know if am not clear with my question.

I have sample loan data in the below format(copied from the attached sheet )

Loan_ID	Month_End	Loan_Amt	Loan_Bal
L1000	31/03/2018	56692	56000
L1000	30/04/2018	56692	55894
L1000	31/05/2018	56692	55065
L1000	30/06/2018	56692	54268
L2000	31/07/2018	56692	53421
L2000	31/03/2018	10234	9989
L2000	30/04/2018	10234	9805
L2000	31/05/2018	10234	9620
L2000	30/06/2018	10234	9434
L2000	31/07/2018	10234	9247

Against each loan I need to create two new columns as ,

previous_month_end

previous_loan_bal 

on a rolling basis.

so my output should look like,

Loan_ID	Month_End	Loan_Amt	Loan_Bal	Prev_Month_End	Prev_Loan_Bal
L1000	31/03/2018	56692	56000	28/02/2018
L1000	30/04/2018	56692	55894	30/03/2018	56000
L1000	31/05/2018	56692	55065	30/04/2018	55894
L1000	30/06/2018	56692	54268	30/05/2018	55065
L2000	31/07/2018	56692	53421	30/06/2018	54268
L2000	31/03/2018	10234	9989	28/02/2018
L2000	30/04/2018	10234	9805	30/03/2018	9989
L2000	31/05/2018	10234	9620	30/04/2018	9805
L2000	30/06/2018	10234	9434	30/05/2018	9620
L2000	31/07/2018	10234	9247	30/06/2018	9434

I used a formula to find the previous month end as DateTimeAdd([Month_End],-1,"month"), but am not quite sure as to how to find the loan_bal corresponding to that month .

Any help would be highly appreciated :)

PS - My data volume is quite enormous with close to 60 columns .  

[DavidP · Accepted solution]

Welcome to the Community!

Your formula for getting the previous month end won't always work, for instance, 30-Apr minus 1 month = 30 Mar. So, I'd change the formula to:

DateTimeAdd(DateTimeAdd(DateTimeAdd([Month_End],1,'day'),-1,'month'),-1,'day')

Now you can use a join tool to join month end to previous month end and get your prev balance, like this:

[ramesh_neel]

Thanks heaps for the super quick response ! works like a charm :)