MDX for current and previous month

[M1ndbender]

Hi All,
I have a period dimension that is by day. days roll up to months, months to quarters and quarters to years.
I am trying to create dynamic mdx that will return the current month and previous month

This works to return the current month

Code: Select all

FILTER(TM1SUBSETALL([report_HDD_fiscalPeriodByDay]) , [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.NAME = [CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value]))

And this works for previous month if I hard code CURRENTMEMBER.NAME to the current month

Code: Select all

FILTER(TM1SUBSETALL([report_HDD_fiscalPeriodByDay]) , [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.NAME = [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.PROPERTIES("PreviousPeriod"))

Is there a way to combine these so it will bring back current and previous at the same time?

[MarenC]

Hi,

either of these should work, but there are lots of different ways:

Code: Select all

FILTER(TM1SUBSETALL([report_HDD_fiscalPeriodByDay]) , [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.NAME = [CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value])) + FILTER(TM1SUBSETALL([report_HDD_fiscalPeriodByDay]) , [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.NAME = [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.PROPERTIES("PreviousPeriod"))

Code: Select all

FILTER(TM1SUBSETALL([report_HDD_fiscalPeriodByDay]) , [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.NAME = [CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value]) or [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.NAME = [report_HDD_fiscalPeriodByDay].CURRENTMEMBER.PROPERTIES("PreviousPeriod"))

Maren

[gtonkin]

How about using StrToMember to derive the members based on the text in the control cube or attribute?
You can then just separate each derived member with a comma and enclose in braces to return both in a set.

Code: Select all

{	
StrToMember("[report_HDD_fiscalPeriodByDay].[" + 
[CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value] + "]"),

StrToMember("[report_HDD_fiscalPeriodByDay].[" + 
	StrToMember("[report_HDD_fiscalPeriodByDay].[" + 
	  [CONTROL].([Control Parameters].[Current Month],[CONTROL_m].[S-Value] + 
	  "]").PROPERTIES("PreviousPeriod") + "]")	
}

[M1ndbender]

That seems to have worked perfectly. Thank you so much for your help.

[gtonkin]

Pleasure - glad it worked!