Multi-Row Formula to Calculate Month over Month Balance - Everything is in one sheet

[hellopanda_]

Hi everyone! 

Hoping to get a bit of help with figuring out a multi-row formula. I've attached some sample data and this is what i'm trying to do:

- calculate the month over month change in "spot amount" by "unique id" and be able to group them by "balance type"

I am trying to automate the calculation so when I do a month over month spot amount comparison, we can see that Group A1 in 202101 started with a total volume of 1200 (900 continued less -150 matured plus + 450 new) and to get to 202102 volume of 1586, we see that the volume change was 1436 continued -230 matured plus +380 new, for a total new volume of 1586.

I have a pivot table included in the attachment to show kind of what I am trying to achieve, but want to do the calculations in Alteryx so I can output into Tableau and group by Sub Group. Any help would be greatly appreciated! Or tips on other ways to make the calculation more dynamic. Currently I have this process in excel and have each month in a different tab and do Current Month Unique ID less Previous month unique ID to get the increase/decrease of the volume and then i throw the current month totals into a pivot table to get the total maturity and new volumes for the months.  

Liked so ^ but blacked out for privacy.

Any help is greatly appreciated!!!

Britt

[Luke_C]

Hi @hellopanda_ 

Here's some examples that should get you in the right direction:

1. Multi-row (grouped by unique ID) to calc the difference between the periods
2. Summarize to sum the difference by balance type

Let us know if you run into any issues as you build it out.

[hellopanda_]

Hi Luke! Thanks for the tips. Would this till work if there were more than 2 time periods? And I am hoping to use the same logic to calculate blended margins as well...are you familiar with the sumproduct formula in excel? Any tips on how to incorporate that using a multi-row? Really appreciate your help with this!!

[Luke_C]

Hi @hellopanda_ 

Yes, assuming the dates are in order it will work for any number of periods. The first will always have a change of 0 though. Below is a link on sumproduct

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Sumproduct-Excel-formula-in-Alteryx/td...