Exploring TM1 - a Chartertech Company
Search
Close this search box.

How to Roll Forward Values from One Period to the Next in TM1

How do you take a value from one period and roll it to the next in TM1? This is a really simple task in TM1.

Why Would you Pull a Value Forward?

Often when we are doing financial modelling or operational planning in TM1, we need to be able to take closing balance and roll it to the opening balance of the next period.

For example, you might be calculating inventory and your formula might be Closing Balance = Opening Balance + Purchases – Cost of Sales. That’s easy, yeah? Well then the next month you want to use the same formula, but you want to populate the Opening Balance with the Closing Balance from the previous month. That’s easy too and we’ll show you how below.

How to Roll Forward Values in TM1

To do a roll forward in TM1 we need to do a few things:

  • Set up a Prior and Next Period attributes in the time dimensions
  • Add a rule to pull the values from Closing Balance into Opening Balance
  • Add a feeder to push the Closing Balance to activate the Opening Balance cell
  • Check results

Time needed: 30 minutes

How to calculate a value that rolls from one month to the next TM1?

  1. Create Prior and Next Period Attributes

    First of all we need to have prior period attributes.
    If you have Month and Year dimensions, then create a Prior Month and Next Month attributes in the Month dimension and a Prior Year and Next Year attributes in the Year dimension.
    If you have a single, continuous, time dimension with months and years in it, then create a Prior Period and Next Period attributes.
    These attributes can then be interrogated via an ATTRS to enable the roll forward fuel and the feeder.

  2. Write a Rule to Pull Forward the Closing Balance:

    ['Opening Balance'] = DB('Inventory Plan',IF(!Month@='Jan',ATTRS('Year',!Year,'Prior Year'),!Year),ATTRS('Month',!Month,'Prior Month'),!Version,!Product,'Closing Balance','Value');

  3. Create Feeders to Activate the Future Cells

    ['Closing Balance'] => DB('Inventory Plan',!Year,ATTRS('Month',!Month,'Next Month'),!Version,!Product,'Opening Balance','Value');

    ['Dec','Closing Balance'] => DB('Inventory Plan',ATTRS('Year',!Year,'Next Year'),'Jan',!Version,!Product,'Opening Balance','Value');

  4. Review Results

    The final step in carting a roll forward in TM1 is to review the results in a view. Here is the result we have from the above.
    You can see that we start with a zero balance in January and then add on Purchases and subtract Cost of Sales to calculate Closing Balance. This is all in the dimension hierarchy and the weight of Cost of Sales is set to -1 (so it is treated as a negative).
    Then the rule pulls the value from January Closing Balance into February Opening Balance and so on.
    Finally, if we were to change year, the Opening Balance fo rJanuary in 2021 would be the Closing Balance for December 2020.

  • This field is for validation purposes and should be left unchanged.

Post Sections

Related Posts

John Vaughan

John Vaughan

John is a CPA, MBA and has been a Performance Management consultant for over 25 years. He is the founder of ExploringTM1 and highly regarded for his experience combining financial management with corporate planning, reporting and analysis. He lives in Sydney with his wife, two of his three children, their cat, Freckles, a bunch of chooks and some fish. John is a sports nut, who played rugby until he was 40, started playing football at 54 and loves being outdoors.

Leave a Reply

Your email address will not be published. Required fields are marked *

Log In