Alternative to IF Historical Period? formulas

cbrookes

Hi All,

It seems like almost every model I've taken over has relied heavily on something to the effect of:

 

IF Historical Period? THEN Actuals ELSE Forecast

 

I've seen some really ugly formulas that have separate business logic for historical and forecast periods nested within the two conditions. Generally, this issue is easy enough to simplify by breaking out the business logic into multiple line items then applying the historical IF/THEN in one line item. 

The thing I've had trouble simplifying with this type of formula is historical overrides. Essentially you need to add something else that checks for overrides before the historical period such as:

 

IF Override? THEN Override Value ELSE IF Historical Period? THEN Actuals ELSE Forecast

 

This can get overly complicated pretty quickly when you have to add in other business logic. 

I'm wondering it there is a better way to approach using Actuals/Snapshots in historical periods than using an IF/THEN statement like above?

cbrookes

Thanks, @andrewtye. Good suggestions. I'm hoping to get away from the "IF Historical Period? THEN Actuals ELSE Forecast" completely.

I've taken over a lot of models that have a significant number of line items with the above type of formula. Simplifying it the ways I already do or what you suggested increases daisy chaining which further degrades model performance. I feel that I have to choose between the lesser of two evils when formulas are constructed that way, daisy-chaining vs super complicated formulas

Native versions might do the trick. I'll have to play around with them. All my mentors said no one uses native versions anymore so I haven't bothered seeing what they are capable of!