@rob_marshall As a follow up to this question: I now have a similar problem with (fortunately), time dimensioned data. Assume a conversion curve like Below, from 2014 to 2021 (upto 2018 is actuals, and henceforth is forecast), with monthly data: Jan 14, Feb 14, Mar 14, .... Jan 15, Feb 15, Mar 15, .... , Jan 19, Feb 19, Mar 19 ... Conversion% 1.5% / 1/6% / 1.2% / ... 1.6% / 1.0% / 1.1% / .... . <blank> / <blank>/ <blank> ... and so on To extrapolate this curve, I need to consider monthly cohorts or seasonality. i.e, For Jan 19, I want the simple average of Last 5 years of JAN data. i.e., We are adjusting for seasonality for Jan 19 projection by taking only January values from the last 5 years (Therefore, using PREVIOUS() will not work with monthly time dimensioned data). We need a moving average for last 5 years (i.e., if data is blank, we just carry over the latest non blank january data and keep averaging last 5 years for however infinitely into the futrure we want) Assume that this is a time dimensioned module. I am trying an approach where I create 5 line items each one having data like: LAG(Conversion, 12, 0) -- "1yr lag" LAG(Conversion, 24, 0) -- "2yr lag" ... ... and so on till LAG(Conversion, 60, 0) -- "5 yr lag" Another line item to COUNT number of prior year lags that are non-zero (for denominator of average) Finally, Conversion Extrapolated = SUM( all lag line items) / count Is this the best approach? Any other ideas?
... View more