Dynamic input ranges for formula calculations

[Mj9715]

HI All, I have a month wise data set with two columns (A &B) which is the input data.

The output needs to be a 1month, 3month, 6month, 12month and YTD summary using a formula { =(PRODUCT(range/100+1)-1)*100}. Range denotes last three values of col A for a 3month calculation, last six values of col A for 6month calculation and so on. 

Both input and output in same tab of attached file.  Please can someone guide on this?

Input 

Date	Col A	Col B
Jan-20	0.79	0.89
Feb-20	0.62	0.54
Mar-20	0.78	0.53
Apr-20	0.56	0.71
May-20	0.78	0.03
Jun-20	0.61	0.00
Jul-20	0.10	0.42
Aug-20	0.45	0.81
Sep-20	0.14	0.84
Oct-20	0.66	0.34
Nov-20	0.83	0.84
Dec-20	0.36	0.94
Jan-21	0.77	0.09
Feb-21	0.07	0.21
Mar-21	0.66	0.96
Apr-21	0.29	0.67
May-21	0.72	0.40
Jun-21	0.98	0.16
Jul-21	0.41	0.60
Aug-21	0.63	0.44

[Christina_H · Accepted solution]

There might be a more elegant solution, but I've used a series of sample/filter tools to identify the rows of interest for each different time period, then unioned them back together for the calculations.  My results don't quite match yours so I might have gone slightly wrong, hopefully you'll be able to spot where since I haven't found it yet...

Solution for product function taken from:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/How-to-mimic-Excel-PRODUCT-Function-mu...

[Mj9715]

Thanks Christina! I will try this out, I think the output variation is due to the product function used in multi-row tool.