Dynamic Month Formula

[gparks]

Hi everyone- I am working on a dataset that has closed client deals with the amount and close date, I also have a separate dataset that has the financial metrics per client by month. I want to look at for every closed deal, the financial metrics for that month,prior month and next month. Is there a way to use a dynamic formula to take the close date from the sales data and then output the financial metrics of Current Month, Month -1 and Month + 1? I have already joined the two datasets.

The purpose of this is because I am interested in seeing when close date of the sale hits the financial metrics within a 3 month range.

[apathetichell]

Yes - but if you can upload sample data it'll probably help explain how. Otherwise - I can say totally - sort by month and use a multi-row-formula to add [row-1:currentvalue] + [currentvalue]+[row+1:currentvalue] but it might not work with your data and there might be additional steps.

[gparks]

Hey I have attached a sample of my data

[apathetichell]

Thanks!

Just the sale amount and 1 month before/1month forward from close date?

[gparks]

Yup and month of the close

[apathetichell]

I didn't group by ID - but this will use month of close/create a sum for month and then look to find the rolling sum over 3 months.