Rolling Month problem

[MikeG]

Hi

I am currently creating a crosstab with the columns being the last 12 months from the current month.

So I used lastPeriods (12, [Current Month])  However Current Month on my Hierarchy is at a higher level than Months (see attachment) so it reads the members behind it and gives Current Month, Prior Year, Current Year etc

So what I am trying to do is find the current month in the Month Level and read 12 back from there. So how can I do this? I have tried things like  lastPeriods (12, (members (Month Level) = [Current Month])  ) and they don't work

thanks for  looking
Mike

[MFGF]

Hi

I am currently creating a crosstab with the columns being the last 12 months from the current month.

So I used lastPeriods (12, [Current Month])  However Current Month on my Hierarchy is at a higher level than Months (see attachment) so it reads the members behind it and gives Current Month, Prior Year, Current Year etc

So what I am trying to do is find the current month in the Month Level and read 12 back from there. So how can I do this? I have tried things like  lastPeriods (12, (members (Month Level) = [Current Month])  ) and they don't work

thanks for  looking
Mike

Hi,

The issue you have is that the Current Month member is in a different hierarchy from the real month members in your dimension. You can use the linkMember() function to grab the corresponding month member from your real hierarchy.

You're also going to need to get the first child of the Current Month member (from the member tree you posted) , so the firstChild() function should do this.

I think your expression will end up being something like:

lastPeriods(12, linkMember (firstChild ([Current Month]),[your Month level from the hierarchy]))

Cheers!

MF.

[MikeG]

Hi MFGF,

Thanks for replying so quickly. I should have mentioned that I am using TM1 cubes and unfortunately linkMember isn't supported for them. Is their a TM1 equivalent ?

Best regards
Mike