Using the ANCESTOR function on a DATE dimension

Question

Here is my script:

WITH MEMBER [Date].[Date - Calendar Month].[2MthPrev] AS
    (
    ANCESTOR(
        CLOSINGPERIOD([Date].[Date - Calendar Month].[Calendar Month]),
        2
    ))
SELECT  
    NON EMPTY 
    {
        [Date].[Date - Calendar Month].[2MthPrev]
    }
    ON ROWS,
    NON EMPTY
        [Measures].[Revenue]
    ON COLUMNS
FROM [OurCube] 

The query runs with no error but the result pane is empty.

I've attempted to create a custom member in the [Date - Calendar Month] hierarchy that is two months previous to the last month in the hierarchy's level [Calendar Month]. So if the last loaded month in the cube is July 2013 then I'd hope that [2MthPrev] would show the results from May 2013.

I believe the problem is with the custom member [2MthPrev] and its use of ANCESTOR - how is this fixed?

Answer 1

This query returns 2 months prior from the last populated date for the given measure group. You may have to fiddle with it to make a calculated member. The second argument in tail is optional. If you don't include it, the default value is 1.

So I'm returning the item that is 2 prior to (lag) the first item (Item(0)) of the set which includes the last month (tail) from the set of months for which there are values in the Measure Group (exists clause).

select {Tail(Exists([Date].[Date - Calendar Month].[Calendar Month].members, , "Measure Group Name")).Item(0).lag(2)} on 0
 from [OurCube]