How to filter using calculated date value based on parameter in MDX

Question

I'm trying to fetch the values based on a Date value passed as parameter (named AsOnDate) into a Performance Point report's MDX from a Webpart Date Filter. It's an Accounts database and the scenario is to fetch the Dormant accounts data. The problem I'm facing is that I need to fetch the Accounts having Dormant Date till 6 months before the AsOnDate value, i.e. AsOnDate -6 months, I managed to calculate the 6 month old date using DateAdd() function, like following:

DateAdd('m', -6, DateValue('<<AsOnDate>>'))

Now, I cann't figure out how to pass this value in place of the paramter in WHERE clause, MDX query looks like:

WITH 

SET [Products] AS { <<Product>> } 

MEMBER [Measures].[Dormant A/C Count] AS ([Measures].[Account Head Count]) 

MEMBER [Measures].[Dormant A/C Vol.] AS ([Measures].[LC Balance]) 

MEMBER [AH].[Subhead - Description].[Totals] AS Aggregate([Products]) 

SELECT 

{ [Measures].[Dormant A/C Count], [Measures].[Dormant A/C Vol.]} 
ON COLUMNS, 

{[Products], [AH].[Subhead - Description].[Totals]} 
ON ROWS 

FROM 
[MyCUbe] 

WHERE (

[AH].[Is Dormant].&[1], 
[AH].[Is Inoperative].&[0], 
{NULL : [AH].[Dormant Date].&[ DateAdd('m', -6, DateValue('<<AsOnDate>>')) ]} 

)

I get this error:

Error running data source query.
The 'DateAdd('m', -6, DateValue('2012-12-03'))' 
string cannot be converted to the date type.

I have tried StrToMember() like:

WHERE ( 
[AH].[Is Dormant].&[1], [AH].[Is Inoperative].&[0], 
{ NULL : StrToMember("[AH].[Dormant Date].&["+DateAdd('m', -6, DateValue('<<AsOnDate>>'))+"]")} 
)

However, passing the Date Filter value directly as following works fine:

WHERE ( [AH].[Is Dormant].&[1], [AH].[Is Inoperative].&[0], 
{ NULL : [AH].[Dormant Date].&[<<AsOnDate>>T00:00:00]} )

Shakeel Tariq Shakeel Tariq · Accepted Answer · 2013-06-08T18:07:07

Figured it out, after some hit n trials.. managed to hack a combination of StrToMember() and Format() to convert calculated Date into string and feed it into MDX as Member, here's the working WHERE clause:

WHERE ( [AH].[Is Dormant].&[1], [AH].[Is Inoperative].&[0], 
{ NULL : StrToMember("[AH].[Dormant Date].&[" + Format(DateAdd('m', -6, DateValue('<<AsOnDate>>')), 'yyyy-MM-dd') + "T00:00:00]") } )

Hope it will prove helpful for someone out there..

How to filter using calculated date value based on parameter in MDX

1 Answers