I'm trying to modify the expression below to exclude 2 month members 'Current' and 'Prior':
lastperiods(13,closingPeriod([Financials].[GL Post Date].[Calendar].[Month],#prompt('PSelectedTimePeriod','memberuniquename','[Financials].[GL Post Date].[Calendar].[(All)]->:[M12].[GL Post Date].[Calendar].[All Calendar]')#))
Tried this and several other variations:
lastperiods(13,item(FILTER( [Financials].[GL Post Date].[Calendar].[Month],
closingPeriod([Financials].[GL Post Date].[Calendar].[Month],#prompt('PSelectedTimePeriod','memberuniquename',
'[Financials].[GL Post Date].[Calendar].[(All)]->:[M12].[GL Post Date].[Calendar].[All Calendar]')#)) not in ('Current','Prior')) ,0)
Can someone help me figure out what I've got wrong? Thanks.
Quote from: jway on 07 Sep 2017 04:57:38 PM
I'm trying to modify the expression below to exclude 2 month members 'Current' and 'Prior':
lastperiods(13,closingPeriod([Financials].[GL Post Date].[Calendar].[Month],#prompt('PSelectedTimePeriod','memberuniquename','[Financials].[GL Post Date].[Calendar].[(All)]->:[M12].[GL Post Date].[Calendar].[All Calendar]')#))
Tried this and several other variations:
lastperiods(13,item(FILTER( [Financials].[GL Post Date].[Calendar].[Month],
closingPeriod([Financials].[GL Post Date].[Calendar].[Month],#prompt('PSelectedTimePeriod','memberuniquename',
'[Financials].[GL Post Date].[Calendar].[(All)]->:[M12].[GL Post Date].[Calendar].[All Calendar]')#)) not in ('Current','Prior')) ,0)
Can someone help me figure out what I've got wrong? Thanks.
Hi,
Can you advise whether these two members are in the set returned by the first expression? Or are they coming from an alternate hierarchy?
If the members are in the original set, you can use an except() function to return the set minus those two members.
If not, then I guess your second expression is trying to filter the set based on the captions of the two members to exclude? In this case, you're going to need to use a caption() function or a roleValue() function to isolate the captions first
eg
filter( <your original set of 13 members>, caption(<your original set of 13 members>) not in ('Current','Prior'))
Cheers!
MF.