Using periodsToDate function with members in different Hierarchies??

[sconti11]

I am attempting to create a dynamic YTD column in my crosstab, but the issue I am running into is the fact that for my MDX command periodsToDate I have to pull members from two different hierarchies.

Here is the expression I am working with:

periodsToDate([Fiscal Year],#prompt('pClosingMonth','token',[Last Closed Fiscal Mo YTD]')#)

I have been trying to figure out how to manipulate that last part of the function to allow the member value from the other hierarchy without it knowing it is from there.

I thought it may be possible to create a data item that can be used for the 2nd part of the periodsToDate argument...and this would actually be a member from the 1st hierarchy that matches the member from the 2nd...so that this way the periodsToDate function will not error off since they will be in the same hierarchy??

Any suggestions?

 

[sconti11]

Ok..here is what I figured out...it is not pretty but it does the job. Here is the code:

periodsToDate([Crown Margin Analysis - National Cube].[Time Dim].[Fiscal Time Periods].[Fiscal Year], #prompt('pClosingMonth','token','linkMember(lastChild(lastChild([Crown Margin Analysis - National Cube].[Time Dim].[Last Closed Fiscal Mo Grouped]->:[PC].[@MEMBER].[Last Closed Fiscal Mo YTD (TY)])), [Crown Margin Analysis - National Cube].[Time Dim].[Fiscal Time Periods].[Fiscal Month])')#)

If anyone has a better way of doing this...please let me know

Thanks