Creating Formula Question

neg177
neg177
in Modeling

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 

Tagged:

Best Answer

  • JaredDolich
    JaredDolich
    Answer ✓

    @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)

     

     

Answers

  • neg177
    neg177

    I'm going to try it. 

     

    Thank you 

  • neg177
    neg177

    Hi,

     

    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]

     

     

    Thank you 

Categories