Finding Average based on column name criteria

[csprch]

Hi I have a dataset where prices are entered on columns with their exact dates but I intend to transform the data into a table that finds the average of the Months based on the column dates. 

I have provided an example to illustrate my current database. 

An example of my current data is : 

I would want to create a table that output would look like this: 

I intend to create a table that would be able to update with new data every month so could anyone guide me in the right direction?

Thank you!

[JosephSerpis]

Hi @csprch I mocked up a workflow that I think answers your question. Let me know what you think?

[estherb47 · Accepted solution]

Hi @csprch 

You can transpose the data, and then use a formula tool to create a new header based on the date. This function will pull just the month and year out: Substring([Name],FindString([name], " ")+1,length([Name])-FindString([name], " "))

Then a crosstab with Average builds back the desired table.

Let me know if this helps. You can add a dynamic rename to make the column headers exactly as desired, at the end. Rename by a formula on the 2 Month columns. Regex_Replace([_CurrentField_]),"(.*)_(.*)_(.*)","$2 $3 $1")

Cheers!

Esther