I have a multi-dimenstional model with data summarized up to the month for the past 3 years. In additon to the dimension with all the months, I have 2 Date dimensions with the following attributes:
- Last 12 months (contains children members of each of the last 12 months)
- Prior 12 months (contains children members of each of the last 13 to 24 months)
I am interested in doing This Year vs. Last Year Analysis to create a line graph where
- x-Axis: rolling 12 months where the most recent is on the far right
y-Axis: Quantity Sold
Series: Last 12 Months and Prior Last 12 months
My goal is to produce a graph similar to the attached (Made in excel). I tried to use the 'cousin' formula, but failed. Any help would be awesome.
Thanks
When you're using a multidimensional model, you really don't need to create more than one dimension to describe the same dates. In this case, you want to show the current month and the 11 previous.
There are several ways of finding the current month, but without knowing more about your data I'll leave that to you. Instead, I'll assume you're calling July 2014 directly.
So in the categories you should have:
lastPeriods(12,[July 2014])
The measures are what you need to modify to get the rolling values.
For the current year, you simply need to find the last 12 months for each category.
total([Measure] within set lastPeriods(12,currentMember([Cube].[Time Dimension].[Time Hierarchy])))
By using the currentMember, you're ensuring that the measure takes the previous 12 months for each month in the categories.
The same logic can be used for the previous year.
total([Measure] within set lastPeriods(12,parallelPeriod([Cube].[Time Dimension].[Time Hierarchy].[Year Level],1, currentMember([Cube].[Time Dimension].[Time Hierarchy]))))
Since the currentMember function returns a member, we can wrap that in any function that takes members as a parameter. So the parallelPeriod function allows us to say that we want to take the current month, and find the same month in the previous year. If we used quarter instead of year it would give us, for example, April 2014.
Thanks for this. But I am actually lookig for the revenue for each month, not a rolling sum. I tried your suggestion and it works, but can you show me how to do it without the Total?
Got it. A tuple with parallelPeriod should work well:
tuple([Measure],
parallelPeriod([Cube].[Time Dimension].[Time Hierarchy].[Year Level],1, currentMember([Cube].[Time Dimension].[Time Hierarchy]))