Wednesday, March 21, 2012

ParallelPeriod Calculated Member - Don't want to tie to year

I currently have Calculated Member in a cube that shows ParallelPeriod using a hierachy based off the year. So you always get prior year amounts, even while drilling into the hierarchy. Here is this Calculated Member (nothing special). This takes the amount you are on and subtracts the previous years amount.

Code Snippet

[Measures].[Amount] - ([Measures].[Amount], ParallelPeriod([Date].[Year - Quarter - Month].[Year], 1, [Date].[Year - Quarter - Month].CurrentMember))

What I am looking to do is always show the prior period based on where you are in the hierarchy (and do it with one calculated member). So I want to take amount and subtract it by the previous period based on where you are in the hierachy (Q2 2007 - Q1 2007, and not Q2 2007 - Q2 2006). This would be an example.

Hierarchy is Year - Quarter - Month. When looking at Years, you see previous year....when you drill into Quarter, you see the previous Quarter of the same year (so you see Q2, 2007 compared to Q1, 2007 and not Q2, 2006). And so on.

Hope that makes sense. Any help is greatly appreciated.

Would PrevSibling be applicable here.

Steve

|||Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)
|||

Jeffrey Wang wrote:

Try [Measures].[Amount] - ([Measures].[Amount], [Date].[Year - Quarter - Month].PrevMember)

This seems to work. I knew it was something straightforward. Thank you very much for your help!

No comments:

Post a Comment