Current 3 months rolling data by Measure

Hello,

I am trying to get 3 months of rolling data for the attached file. I have tried several approaches I have seen posted and can't seem to get it right. Each measure could potentially have a different begin data and this data is sent up one row per month. so I need the rolling 3 months to group the Begin Date rows by measure.

Test Data for Alteryx question.xlsx

Datasets

Date Time

Accepted answers

OllieClarke

Hey @aberthiaume

Here's how I'd approach this:

So we join the prior 2 months, plus current month onto each row, and then find the avg of those values.

Hope that helps,

Ollie

mvgavg.yxzp

All comments

Brian32

If you use the Multi Row Formula you should be able to accomplish this. You will just want to group by ID and Measure in the configuration. Prior to that just sort, by ID, Measure, and Date. Hope this helps!

Brian

SPetrie

One possible solution: Summarize the data grouping by Measure and get the last Begin Date. That will give you the most recent date for each measure type. Using a formula tool to subtract 2 months from that date will give you the first month of your rolling 3 month period. You can find/replace based off of measure name back on the original data and filter out anything that doesnt start on or after the 3 month beginning date.