Need Help calculating %Var in multiple ways from appending data set

[Anonymous]

I'm banging my head against the wall on this, and would appreciate any help...

Every month, I collect a global forecast. It's relatively easy for me to do analysis of the latest forecast, but what I really want to do is analyze changes in the forecast. To that end, I have a single excel file, and I append the latest forecast data to it as I receive it. The fist column in my table is the "forecast published" column, so I know which month the forecast was published. The table below is a very simple example.

Objective 1: 

Calculate the %variance ((new-old)/old) between the forecast year and the baseline year (2019, or user-selectable). So for example, I would use a filter to select the published forecast of interest (say, March), then show the forecast units for 2019-2022 and also the %change for 2020-2022 vs. 2019. It would be even better if I could make the baseline year selectable, but that's harder I suspect?

Objective 1

Objective 2: 

Calculate the %variance across each successive forecast publish, by year. For example, i want to show how the forecasted units are increasing for years 2020 - 2022 between earlier (Jan) and later (Mar) published forecasts.

Objective 2

Example data set:

Forecast Published	Forecast Year	Units
Jan	2019	16,315,117
Jan	2020	12,760,640
Jan	2021	14,611,281
Jan	2022	15,705,718
Feb	2019	16,315,117
Feb	2020	12,903,781
Feb	2021	15,180,948
Feb	2022	15,802,295
March	2019	16,314,403
March	2020	13,019,088
March	2021	15,905,071
March	2022	16,295,605

Any help is greatly appreciated. Also, this is my first time posting, so please excuse any breaches of etiquette in how I posted.

[Anonymous]

Thank you!!! I had to play with it a bit, but your solution worked for me.

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.