Finding historical averages

[AlterIT]

I have a dataset with few years of historical orders and sales. I am trying to come up with a 3 Yr Avg% and 5 Yr Avg % which is essentially 3 Yr Sum (Orders)/3 yr Sum (Sales) and 5 Yr is based on historical 5 Years (For each state and each week) . Only caveat is it has to ignore the latest year available in dataset, e.g. in current example it will ignore 2021 information and goes further 3 and 5 year back. I have attached dataset in the excel with current and expected  views.

[AngelosPachis]

Hi @AlterIT ,

Here's one way you can do it. I've transposed your table so all values for sales and orders extend vertically. Then I've parsed the year from the column headers and found the max year in the dataset.

Then for those that are 3 years back to the max year-1 (max year is excluded) and 5 years back I have estimated the averages of orders to sales and joined back to the master table

Hope that helps,

Angelos

[AlterIT]

Thanks, that seems to be it. However I keep seeing this error while I go through the components

[AngelosPachis · Accepted solution]

Hi @AlterIT ,

Do you mind trying to run the workflow? If you run it, that error should go away and the workflow should run fine

[AlterIT]

Thanks a lot !! with this help

[Robert_BlackeyDM]

-