Sales vs Forecast from prior period

[leivers1]

Hi,

I am trying to compare using a line chart by month year the sales versus  the forecast amount. However a forecast amount can change from one forecast cycle to another. Eg. We do a 24 month forecast for our  June 2020 sales in Jan, Feb etc.

I want to compare the amount that was sold against what was forecast for a month but being able to change the forecast cycle from  -1,-2,-3...-12  from that month year.

i want columns with the total qty by month year of forecast cycles from 1 to 12 months ago so that i can change in the chart the forecast cycle i want to compare against.

Yellow box here is a looking a forecast cycle 3 months previous of the month year.

This will be used to calculate the MAPE so would like to do it in the load.

See sample chart below, explanation and data model

Thanks,

Laura