Im trying to create a model that shows forecast accuracy development over time in for example a bar chart.
I have a rolling 12 month sale forecast that updates every month.
Forecast 1 - Issued in January - Covering Jan 2019 -Dec 2019
Forecast 2 - Issued in February - Covering Feb 2019 - Jan 2020
Actual data on same format
I would like to compare each forecast against the actual outcome of sales. And I like to be able to tell the model how many months of each forecast that should be shown in the chart (for example "only include the first 3 months of the forecast", i.e. for Forecast 1 Jan-Mar and for Forecast 2 Feb-Apr). The question I'd like to answer is "How well did we forecast the 'n' first months of each forecast?"
Below example would show that in
Forecast 1 the actual outcome of January - March (i.e. forecast month 1-3 in forecast 1) was 3% below forecast
Forecast 2 the actual outcome of February - April (forecast month 1-3 in forecast 2) was 2% above forecast
Above this a slizer to choose number of months to include (for example forecast month 1, 2, and 3)
I have tried diffent ways but im not getting there.