I'm trying to make a month pick list to be used as a parameter.  What I have so far gives me all months which have sales which is good.  What I'm having trouble with is excluding the current month.  I only want to see the months that are complete.  So if it's August, I only want to see January through July.  Thanks in advance.

_________________________________________

WITH
MEMBER [Measures].[ParameterCaption] AS [Time].[Month].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterValue] AS [Time].[Month].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS [Time].[Month].CURRENTMEMBER.LEVEL.ORDINAL

SELECT
{
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel],
[Measures].[Ship Resale - SALE]
}
ON COLUMNS ,

FILTER([Time].[Month].[Month],[Measures].[Ship Resale - SALE] <> 0) ON ROWS

FROM [Sales]

WHERE STRTOSET(@pickTimeYear )

_______________________________________________

Hi,

I am not sure what format you have your month member in, but you could use something like

EXCEPT( FILTER([Time].[Month].[Month],[Measures].[Ship Resale - SALE] <> 0)

           , STRTOMEMBER( "[Time].[Month].[Month].&[" + Format(Now(),"yyyy MM") + "], CONSTRAINED )

            ) ON ROWS

I haven't tested above, just written.

So basically it would remove the current month from the set.  There are probably many many ways to do this really, but just a quick option.

Chris

Hi tlovley,

According to your description, you want to always exclude the current month in your MDX query. Right?

In this scenario, you can have your set exclude the current month member based on Now() as Chris suggested. And I think the current month should always be the last member on month level. So you can also have your set open period to the last second month. I tested on Year level, the expression can be like:

(null:closingperiod([Date].[Calendar].[Calendar Year]).lag(1))

Regards,