I have a couple of accounts that use the previous month value across years. However, sometimes it's necessary update the value in the middle of the year and this change should be reflected in the following months and years mantaining the old value in previous months and years.
Most of my applications have some sort of "Adjustment" dimension. It usually looks something like this:
- Total Adjusted
All Actuals data and most input forms load data into the "Base" member. However, most calculations look at the "Total Adjusted" member. In your case, your @PRIOR member formulas would simply point to the "Total Adjusted" member. Drop some data in the "Adj" member, and it will carry forward.
There are other benefits to this approach:
- Developers can create separate adjustment buckets. With the appropriate security in place you can see which group adjusted what.
- Developers can create "Override" members as well as "Adjustment" members.
- Any member combination can be adjusted (as opposed to simply creating adjustment accounts).
I think I understand what you said, but i have some questions:
- In your "Adjustments" dimension you have any kind of IF clause in order to fill "Total Adjusted"?
IF "Adj" !== #missing THEN "Total Adjustments" = "Adj" ELSE "Total Adjustments" = "Base", or user will input the difference in "Adj" account and it will be summed or subtracted to "base" account?
- You have different data forms to "Adj" and "Base" member, and a final form with "Total Adjusted" or the Adjusted dimension members are page filters in only one data form?
I typically just aggregate up the Base + Adj to get "Total Adjusted". No need for a formula, unless you want to introduce specific override functionality. (Where the override amount would ignore what's in "Base" and "Adj") You may be able to get away with making "Total Adjusted" dynamic calc. This will depend on a lot of different factors, but it's something you should consider.
Sometimes I put my adjustments into a separate form. Sometimes they're on the same form. That really depends on business requirements.
Let's say you don't want to make this a separate dimension. Then the next option is typically creating specific adjustment accounts. The accounts where you're carrying the balance forward would look to see if an adjustment has been made in one of these specific adjustment accounts, and if so, add that adjustment to the prior value. I tend to prefer a separate adjustment dimension as it offers more flexibility, but if you've already got a database built, adding another dimension can be painful.