Calculating Monthly Change in Financial Accounts

[dishamishra]

Hi there Alteryx community! I have a question which I was hoping to get some ideas for.

I am trying to create a single row of data which calculates for me the change in Accounts Receivable (and other working capital line items) on a monthly basis. I am not missing any months, and my data looks like this:

Accounts Receivable Closing Balances per Month

ACCOUNT_NAME	PERIOD_END_DATE	CLOSING
Accounts receivable	1/31/2018	100
Accounts receivable	2/28/2018	200
Accounts receivable	3/31/2018	300
Accounts receivable	4/30/2018	355
Accounts receivable	5/31/2018	410
Accounts receivable	6/30/2018	405
Accounts receivable	7/31/2018	460

I essentially would like my output to look like this:

Account	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18
Accounts Receivable	0	-100	-100	-55	-55	5

As you can see, my requirements are as follows:

• Find the previous month and calculate the closing balance difference from the current month (i.e. Previous minus Current Month)
• Flip the orientation from vertical to horizontal
• Change the period end type to a "%b-%y" format in Alteryx

So far I can think of using these tools, but I'm not sure how to put it all together:

• Multi-Row Formula
• Cross Tab
• DateTimeFormat function

Any help is greatly appreciated!

[Emil_Kos · Accepted solution]

Hi @dishamishra,

I see my result differ but it should be helpful anyway.

[ImadZidan · Accepted solution]

Hello @dishamishra ,

Instructions were spot on.

Have a look. 

[SubratDas5]

Hi @dishamishra ,

Here's how i think you can do it. Hope it helps. 

Attached.

[dishamishra]

Hi @ImadZidan !

Thank you so much for sharing this solution. I used it in combination with @Emil_Kos 's solution and it worked perfectly!

[dishamishra]

Thanks for your suggestion @SubratDas5 !