Creating Formula Question

neg177

Hi,

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. 

 

My attempt

 

END(Line Item (1) OFFSET( Closing Balance  of Line Item (2), ?, ?) 

 

Please advise, 

 

Thank you 

JaredDolich

@neg177 

Here's are two ideas.

1. 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.
2. 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)