# Alteryx designer Discussions

SOLVED

## Month against previous months on a rolling basis

Highlighted
Meteor

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 .

Highlighted
Pulsar

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: