Hi everyone,

I’m looking for some advice on a data analysis challenge I’m facing. I have a metric that gives me my actuals each month, and I want to set both my forecast and budget for the upcoming period to match the amount from my most recent actual month.

I’ve already tried using functions like PREVIOUS, IF DEFINED, or ‘IF O then 0, ELSE METRIC’ but the size reachs the limit.

However, I’ve run into an issue: when the current month’s actual is not defined or is zero, my formula keeps searching backward until it finds the last defined value, and uses that for the forecast.

What I want instead is: if the current month’s actual is zero or not defined, the forecast should also be zero or not defined for that month-it shouldn’t go back to the last available value.

Has anyone solved this kind of problem? What’s the best way to make sure the forecast only uses the actual value from the immediate previous month, and doesn’t look further back if the value is missing or zero? Any advice on formulas, functions, or approaches would be really appreciated.

Thank you very much,

Thomas