Creating monthly averages

[Rclewright]

We were given a dataset with 5 years of data, 12 months per year, and multiple prices per month (sales pricing). We are trying to average each month's sales pricing so that we can run a regression against multiple raw material indices. To run the regression, we can only have one average price per month.

We are having difficulty averaging/aggregating all of the pricing by month/year. In the end, we should have 60 average prices. So far the only way we can think to do it would be to create 5 yearly filters, and then 12 monthly filters off of each yearly filter and then average each output. Surely there has to be a quicker way to calculate month per year averages. Any help would be greatly appreciated! 

[mceleavey · Accepted solution]

Hi @Rclewright ,

can you provide some mock data showing what you have and what you want?

Thanks,

M.

[JarekSkudrzyk · Accepted solution]

@Rclewright 

Hi, have you tried summarising the data with "summarize" tool - grouping by year, month and averaging sales price?

If it does not work please share an example set of data (dummy).

[Rclewright]

This worked perfectly, thank you so much!

[JarekSkudrzyk]

@Rclewright 

you are welcome, glad I could help you