I am currently using a number of statistical accounts that contain adjustment factors used in calculated accounts. These factors are consistent throughout the year and I would like to only store these factors in December of each year to reduce the system load. What I have not been able to find is how I can point the calculated account to always look at the last month of the year. I have had partial success using [Time].[Default].LastSibling but that only works to pull from the last member in the quarter, but not the year.
Thank you for the suggestion. I am not sure that this will fit in my situation as I am looking to use this in a calculated account to dynamically reference the last month of the same year as the current member. I tried using LastPeriods(1,[Time].[Default].[Month].CurrentMember) but end up with an error. That could also be due to my misunderstanding of the function.
To add a little more context and an update, I was able to get the desired result by pulling the value at the Year level using:
Ancestor([Time].Default.Currentmember, [Time].Default.[Year])
This works because the Time Conversion on the account with the adjustment factor is “Average (excluding empty)” and the calculation this is going into is a Leaf Formula. By only loading the adjustment factor into December the values are the same. I am not sure if this is the most efficient or ideal method pulling from the Year level instead of the Month, but it gets the correct result.
LastPeriods, results in previous period, if you use first period of next year as filter than it will always bring Dec of last year, that way you can be exact regarding the Period.
However, if you want YTD value than your formula looks better as it will always bring Year value of whatever the CurrentMember may be.
@justin.buttles, this was going to be my suggested approach. Glad you found it! Though I was going to suggest using “last excluding empty” or even “last including empty” if you’re sure it will always be December and want the calculation to go to 0 if you haven’t provided a value yet.
(I like your Average approach, though, because that resolves the missing value problem nicely and intuitively…I’ll probably make more use of that myself now that you’ve brought it to my attention.)
We use a number of ways to look up different months during the year - what about setting up a new ‘named set time’ that you can use in your calculations but you can change the month in that time to look at December (or whichever month you want)?
One option I’ve used is
Create an alternate hierarchy in the time dimension that is a flat structure i.e. Year >> Months
Let’s say this is called “YearMonth”
The below MDX will then point to the last member of the YearMonth hierarchy (aforementioned above)
([Time Perspective].[Default].&[BASE],[Time].[YearMonth].CurrentMember.LastSibling)
I knew I could count on you coming up with a solution! I hadn’t thought about using the alternate hierarchy to remove the quarters, but that gives a great solution and allows the use of LastSibling for the full year.
This will help clean up thousands of those excess conversion factors we use in some of our cubes, and will be helpful in other areas where the time conversion may be different than Average excluding blanks.
