How to avoid references to specific years in a formula

michael_gould

I've just been asked about a problem with rolling forward a model from 2011 to 2012. The model had references to full year values, which had been made using a formula like this: Full Year Value = Source[SELECT: Time.FY11] While this works, it requires the formula to be updated every year when the timescale for the model is rolled forward. A better approach is to use the YEARVALUE() function: Full Year Value = YEARVALUE(Source) For each period this gives the corresponding year total from the source line item.  This isn't directly equivalent of the fixed reference to the FY11 total, but it will give the same result if timescale only spans one year. The advantage is that the reference still works when the timescale is rolled on. There are other functions QUARTERVALUE, MONTHVALUE and WEEKVALUE for referencing quarter, month or week totals. The last two are applicable if you’re using a daily or weekly timescale.