No, LAG won't work here as I am looking for Sum of Last 12 periods with respect to the Current Month.
But if we change the Current Month i.e. shift from Jan'21 to Feb'21, my Forecast Line Item will calculate new values and will over-write previous Forecast values when Current Month was Jan'21 for the Jan'21 period. So, in brief: Current Year Months = Jan'21 till Dec'21: The Forecast values shouldn't change for (historical current year months)
I'm not entirely sure I understand what's needed. Here's what I have interpreted it as: So we do a different TIMESUM for each period, unless you don't have data 12 months in advance of that in which case it shows the previous value. Kind of matches your spreadsheet...
You could add a Timerange with the extra year in the future (FY22) and this would work. Hide that future year on the grid. This allows you to do the ITEM(Time) + 12 for Current Period (or set manually) for all of FY21