Prior 12 month average

[M_ThompsonWatermark]

Have a set of data.  750k records.   Large number of fields. Only really need 2 fields and create a new calculated one.

I want to sum up those 750k to look like this:  1 record per dept, per month.  I need to solve for Average of 12 prior months.  

ie if the month is July 1, 2017, I need average of July 1 2016  - June 30 2017.   One row per month as laid out in the table.

Dept	Month	Average $$ Prior 12 month
Dept 1	Jan 1 2017
Dept 2	Jan 1 2017
Dept 3	Jan 1 2017
Dept 4	Jan 1 2017
Dept 5	Jan 1 2017
Dept 1	Feb 1 2017
Dept 2	Feb 1 2017
Dept 3	Feb 1 2017
Dept 4	Feb 1 2017
Dept 5	Feb 1 2017
Dept 1	mm/dd/yyyy
Dept 2	mm/dd/yyyy
Dept 3	mm/dd/yyyy
Dept 4	mm/dd/yyyy
Dept 5	mm/dd/yyyy

So in my data: I have a dept, a date, a paid column. 

I have a date column that is the first day of the month of the given date.

Don't believe it should be that hard.  Thanks in advance.

[ddiesel]

HI @M_ThompsonWatermark!

I think I understand from your post what you're trying to do. Here's how I might tackle it:

1) Summarize (Group by Dept, Group by Month, and Sum_Paid)

2) Sort by Date

3) Sample the last 12 records to isolate the previous 12 months

4) Summarize (Group by Department and Average_Sum_Paid)

Let us know if that gets you close enough. If not, please provide a sample input and desired output.

[M_ThompsonWatermark]

No, apparently I didn't explain it well.

750K records. Each is a record of something paid. Each dept paid several thousand paid records (invoices) a month. If you were looking at if for 'that' month, you'd say what was the average paid for that current month.  But I don't want an average of just that month. for each month I want an average of all the paids for the 12 months prior. So it's a 'rolling' average. Each month is identifying the average of all the invoices paid the prior 12 months. (for that dept).

Does  that help?

[ChrisTX]

Searching the forum for "rolling" yields a few interesting posts

[neilgallen · Accepted solution]

@M_ThompsonWatermark I would recommend checking out the "Moving Summarize" tool within the Crew Macro Pack. This will calculate moving averages (among other things) across any number of rows specified.

That's the easiest route, compared to setting up a multi-row formula tool that requires inputs for at least 12 rows. Also allows the greatest flexibility should your time window change in the future.

Good luck!

[ddiesel]

@neilgallen Thanks for bringing up the Moving Summarize tool! I didn't even know that was in the Crew Macro Pack! Very helpful!

@M_ThompsonWatermark Thanks for your reply. I think I now understand what you're trying to do. There may be a less complicated way to solve this, but this is all I could think of based on your description.

Workflow:

Macro:

Output:

Please give this a try and let us know if it works for you!

[M_ThompsonWatermark]

While the crew macro didn't turn out to be the complete answer, it contained a key element in the approach I used for answering my dilemma. I used a couple of summary tools in a row along with the crew macro, and was able to get exactly what I needed. Thanks!!