Date comparison in MDX

i have a question concerning MDX:

i have the following mdx statement:

with set [Last18month] as strtoset('{[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].lag(17):[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]}')

select

{(([Policy].[Next Status].& ), [Measures].[PolicyCount]),

(([Policy].[Next Status].&[R] ), [Measures].[PolicyCount])} on columns,

[Last18month] on rows
from [PolicySales];

and i want to add a filter according [Policy].[Next Status Date] is within the selected month,

for example if the selected month is [Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00],i want to filter according to [Policy].[Next Status Date] is between [2005-12-01T00:00:00] and

[2005-12-31T00:00:00]

Can you please help me

Regards

Assuming that the .MemberValue for both [Next Status Date] and Month attributes are defined as DateTime values (eg: [Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].MemberValue is 2005-12-01T00:00:00), a "filtered" [PolicyCount] could be created, like:

with

set [Last18month] as strtoset('{[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00].lag(17):[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]}')

member [Measures].[NextStatusCount] as

Aggregate(Filter([Policy].[Next Status Date].[Next Status Date],

[Policy].[Next Status Date].MemberValue >= [Dim Time].[hYearMonthQuarterRS].MemberValue and

[Policy].[Next Status Date].MemberValue < [Dim Time].[hYearMonthQuarterRS].NextMember.MemberValue),

[Measures].[PolicyCount])

select

{(([Policy].[Next Status].& ), [Measures].[NextStatusCount]),

(([Policy].[Next Status].&[R] ), [Measures].[NextStatusCount])} on columns,

[Last18month] on rows
from [PolicySales]

|||

Dear sir,

thank you very much for your reply,your answer works fine

it's very helpfull,but my real problem is: the selected month used in the filter is a parameter in Reporting services,and the parameter could be:

[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005]

or

[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005]

or

[Dim Time].[hYearMonthQuarterRS].[Year_Name].&[Calendar 2005].&[Quarter 4, 2005].&[2005-12-01T00:00:00]

so how i can replace the value of the parameter selected in ([Dim Time].[hYearMonthQuarterRS].MemberValue) and ( [Dim Time].[hYearMonthQuarterRS].NextMember.MemberValue)

the parameter name is: DimTimehYearMonthQuarterRS

as i told before the parameter can take any value from the hierarchy [Dim Time].[hYearMonthQuarterRS]

this is my mdx used in the reporting services that i want to add the filter to it:

="WITH member nextstatus_E as '(([Policy].[Next Status].& ), [Measures].[PolicyCount])' member nextstatus_R as '(([Policy].[Next Status].&[R] ), [Measures].[PolicyCount])' set [Last18month] as strtoset('{"& parameters!DimTimehYearMonthQuarterRS.Value &".lag(17)" &":"& parameters!DimTimehYearMonthQuarterRS.Value &"}') select {[Measures].[nextstatus_E],[Measures].[nextstatus_R]} on columns,{ ([Last18month] ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { " & parameters!p_selected_month.Value & " } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.PolicyHolderGenderType1, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.CustomerVehicleVehicleUsage, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.AgentAgencyName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.AgentTypeName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.AgentAgentName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimProductProductTypeName, CONSTRAINED) ) ON COLUMNS FROM [PolicySales]))))))) WHERE ( IIF( STRTOSET(@.DimProductProductTypeName, CONSTRAINED).Count = 1, STRTOSET(@.DimProductProductTypeName, CONSTRAINED), [DimProduct].[Product Type Name].currentmember ),IIF( STRTOSET(@.AgentAgentName, CONSTRAINED).Count = 1,STRTOSET(@.AgentAgentName, CONSTRAINED), [Agent].[Agent Name].currentmember ), IIF( STRTOSET(@.AgentTypeName, CONSTRAINED).Count = 1, STRTOSET(@.AgentTypeName, CONSTRAINED), [Agent].[Type Name].currentmember ), IIF( STRTOSET(@.AgentAgencyName, CONSTRAINED).Count = 1, STRTOSET(@.AgentAgencyName, CONSTRAINED), [Agent].[Agency Name].currentmember ), IIF( STRTOSET(@.CustomerVehicleVehicleUsage, CONSTRAINED).Count = 1, STRTOSET(@.CustomerVehicleVehicleUsage, CONSTRAINED), [Customer Vehicle].[Vehicle Usage].currentmember ), IIF( STRTOSET(@.PolicyHolderGenderType1, CONSTRAINED).Count = 1, STRTOSET(@.PolicyHolderGenderType1, CONSTRAINED), [subscription - Policy Holder].[hGender_RS].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS"

Thank you

|||If I understand your query correctly, regardless of which level the parameter is at, the query should report on the [Last18month] from the end. For example, for 2005 selection, it should cover Jul, 2004 to Dec, 2005. So the filter in my query will always be evaluated at the month level for each of 18 rows - unless the filter should use the same range (Jan, 2005 to Dec, 2005) for each of the 18 rows, regardless of the month? If you could give a simple example of a parameter selection, and how the report should get filtered, that would help clarify.