Creating Formula Question
Would like to know how to create a Formula with the following structure for a Line Item (1).
Closing Balance of Line Item (2) as of the end of the month, and this will show/happen only on the 10th of every month.
END(Line Item (1) OFFSET( Closing Balance of Line Item (2), ?, ?)
Here's are two ideas.
- I would take a slightly different approach to this that using OFFSET. Instead, I would create a system module that is by day. Add a BOOLEAN line item that checks for the 10th of the month. Add another month period formatted line item that calculates the previous month for each day. Now, the solution. In your planning module, you can create a conditional that looks up the system module for the BOOLEAN. If it's true then LOOKUP the monthly value using the PERIOD value from your system module.
- Using OFFSET, you can use that same BOOLEAN from solution 1. IF NOT SYS01 Dates.10th of the Month? THEN end of period balance ELSE OFFSET(end of period balance,-10,end of period balance)
I'm going to try it.
I tried both of the formulas below and Anaplan accepted them, but I'm not getting back any values from my Formula for the 10th of every month.
IF NOT SYS01 Dates.10th of the Month? THEN end of period balance ELSE OFFSET(end of period balance,-10,end of period balance)
IF NOT 'S01 Time Settings'.'10th of every Month?' THEN 0 ELSE 'Securitization Master Acct (x5000 Closing)'[LOOKUP: 'S01 Time Settings'.End of the Month]