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

How can I create a formula to find the YTD value of an account?

Question

I set up an account called YTD Net Income on my balance sheet, so that my balance sheet will tie-out each month. In order for the YTD NI account to work I need a formula that pulls in the year-to-date Net income from the income statement. I do not know how to write a formula that will do this. For example, YTD NI for April 2012 would add the net incomes together from the IS sheet for Jan/Feb/March/April. YTD NI for May would be the same concept, just with May added. So it would have to be a formula that could work for all 12 months. Can you help me with this?

Answer

To create a formula to get the YTD Net Income from the Income Statement, you would use a formula to find the cumulative value of the account. Your formula on the Balance Sheet would look something like:

if(fiscalmonth(this)=1,ACCT.NetIncome,ACCT.NetIncome+ACCT.BSNetIncome[time=this-1])

ACCT.NetIncome is the Net Income account from the Income Statement and ACCT.BSNetIncome would be your Net Income account on the Balance Sheet.

Once you have confirmed the formula is returning the expected values, please consider replacing "if" with "iff". This can help avoid circular reference errors, and may also improve performance.