Search code examples
ssasmdx

Return set of dates from hard-coded start point up to yesterday

I'd like to return the set of dates from the 18th of June up to yesterday. My attempt was as follows but I'm getting this error:

Executing the query ...
A calculated member cannot be used as an operand of the Range operator ( : ).
Execution complete

WITH   
    MEMBER [Date].[Date - Calendar Month].[LastDay] AS
        Tail([Date].[Date - Calendar Month].[Calendar Day].members,1)
    SET [Set_Dates] AS   
        {
        [Date].[Date - Calendar Month].[Calendar Day].&[20130618]:
        [Date].[Date - Calendar Month].[LastDay]
        }  
SELECT  
    NON EMPTY 
     [Set_Dates]  
    ON COLUMNS
FROM [myCube]
Solution

  • You might have to play about with the formatting and dimension/attribute names, but try this:

    SELECT
    NULL
ON COLUMNS,
{
    [Date].[Full Date].[2013-07-01] :
    STRTOMEMBER("[Date].[Full Date].[" + Format(DATEADD("d", -1, Now()),"yyyy-MM-dd") + "]", CONSTRAINED)
}
ON ROWS FROM [Cube]