Hi - I have a hierarchy with two levels - L1 and L2 (L1 is the parent of L2). The sales data is at L2 level. The forecasting is carried out at L1 level. The disaggregation logic for the forecasting line item from L2 to L1 should be based on the average proportions of L2 to L1 for the last three month sales.
For example, we are in April now. We should consider the sales proportions between L1 and L2 for Jan, Feb and Mar (see below table):
Last 3M Sales Average
Any forecast in any period generated for L1 item should disaggregate the quantities to the corresponding two items of L2 in 40% and 60% respectively.
Please note the average and the %age doesn't require any time scale and I can't use the MOVINGSUM function hence.