Profancy Technoloy Blog

You may use ClosingPeriod function to do that:

ClosingPeriod
(
[Dim Date].[Date Hierarchy].[Month]
,[Dim Date].[Date Hierarchy].CurrentMember
)

will give you the closing month based on the CurrentMember

Let’s say we have data up to May 2011 date, the above function will return MAY for 2011 and DECEMBER for the prior years.

If you are looking for last non empty member, you may use the following:

Tail
(
NonEmpty
(
[Dim Date].[Date Hierarchy].[Date].MEMBERS
,{[Measures].[measureX]}
)
,1
).Item(0)

Speed up MDX query preparation in Reporting Services by specifying default parameter value. Read more…

WITH SET [ABC] AS [CUSTOMER].[CUSTOMER GEOGRAPHY].[COUNTRY].MEMBERS
MEMBER [MEASURES].[TEST] AS GENERATE([ABC], [ABC].CURRENTMEMBER.NAME, “,”)
SELECT [MEASURES].[TEST]
ON 0
FROM [ADVENTURE WORKS]

Assuming you have a time dimension with hieararchy Year->Quarter->Month [Time H], the following will give you last 12 month’s revenue:

Sum
(
ClosingPeriod
(
[Time H].[Month]
,[Time H].[All]
).Lag(12)
:
ClosingPeriod
(
[Time H].[Month]
,[Time H].[All]
)
,[Measures].[Revenue]
)

It is very common to write MDX queries to get top or bottom items based on revenue or some other metric. Here is an example of BOTTOMCOUNT function:

SELECT { [Measures].[Revenue] } ON COLUMNS ,
BOTTOMCOUNT(NONEMPTY([Customer].[All Customers] ) , ( [Measures].[Revenue] ) ), 10, ( [Measures].[Internet Sales Amount] ) ) ON ROWS
FROM [MY CUBE];