Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

How do I reference an Account at a Fixed Month and Relative Year?

Question

How do I reference an account at a fixed month and relative year? In other words, how do I, for every month, pull values from the previous January/February/March…?  

Discussed here are two options, one being a nested if-statement and the other involving an additional account to hold values for the fixed month.

Answer

Nested If-Statement

Create a nested if-statement that checks the current month, and upon identifying the current month, uses the [time=this-x] modifier to pull the account value from x months before to pull the desired fixed-month’s value. An Excel template is attached to assist in generating the if-statement, where in the template the desired fixed month and formula calculation is inputted to output the desired if-statement.

Additional Account with values only from the Fixed Month

Create an additional account to only store the values for the fixed month in the fixed month (will store zeroes for the other months). Then the formula for the output account will sum the values for the additional account over the entire year via the [time=this.year] modifier.

Account 1: Holds the necessary values for every month
Account 2: Equal to Account 1, but only with a value in the fixed month (if(month(this) = x, Account 1, 0))
Account 3: Equal to ACCT.Account2[time=this.year]which is equivalent to the year's fixed-month value