Combining LastPeriods and Cube reference in MDX

[fleaster]

Hi all,
we currently have 2 x MDX dynamic subsets that (a) extract the current month period and (b) creates a subset of the last 12 months based on what the current month is:

Code: Select all

(a)
{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Period] )}, 
[z_ctrl_measures].([z_ctrl_measures].[Current Month],[z_String].[String 1])
)}

(b)
LastPeriods(12, [Period].[Aug 2016])

Note: (b) is refreshed on a regular basis via another process

...the question is, is it possible to combine these 2 MDX subsets into a new one that returns the last 12 months based on the current month cube reference? Am struggling with the syntax for this e.g.

Code: Select all

???
LastPeriods(12, [Period].([z_ctrl_measures].([z_ctrl_measures].[Current Month],[z_String].[String 1])) )

...so if anyone has any tips on correcting it, it would be much appreciated

thanks!

Matt

[declanr]

I think you just need to use StrToMember around the period before you put it into the second query.

Code: Select all

StrToMember("[Period].[" + [z_ctrl_measures].([z_ctrl_measures].[Current Month],[z_String].[String 1]) +"]")

[fleaster]

Thanks muchly! Have plugged it in and can confirm it now works

Code: Select all

LastPeriods(12, StrToMember("[Period].[" + [z_ctrl_measures].([z_ctrl_measures].[Current Month],[z_String].[String 1]) +"]") )