Current 3 months rolling data by Measure

[aberthiaume]

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.  

[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.

edited to include the workflow sample

[Emmanuel_G]

Hi @aberthiaume ,

I did a test to calculate rolling average of Numerator field.

Find it attached.

[OllieClarke · Accepted solution]

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