How do I build a MDX query that considers only facts that happened in the last 10 days of February?

Question

I have a fact table that has a time dimension, which contains year, month, day and hour.

I was able to find ways to filter things that happened in a given day, or month (simple where/filter by the desired level). But I would like to create an MDX query that filter the results so my cube has information about the facts recorded in the last 10 days of febraury.

Is there anyway I can do it?

Answer 1

Assuming you have all the days of February in your cube, you could use a set inside there WHERE clause.

Something like this..

WHERE ([Date].Month)

Supposing you have a Year-Month-Day-Hour hierarchy in place and there may be some dates missing

Select....... on COLUMN,
....... ON ROWS
FROM ....
WHERE
({[Time].[Month].&[Feb 2015].LastChild.LAG(10) : [Date].[Month].&[Feb 2015].LastChild})

If no dates are missing in the date dim,

select ... ON COLUMNS,
... ON ROWS
FROM ...
WHERE
({[Time].[Date].&[02/19/2015] : [Date].[Date].&[02/28/2015]})

If you want the sales for last 10 days of Feb for every year:

SELECT Measures.Sales ON COLUMNS,
       Products.Products.MEMBERS ON ROWS
FROM 
( 
    SELECT
    generate //This would build the set for the last 10 days of Feb for every year
            (
             [Time].[Year].[All].children, 
             TAIL //This returns the last 10 days of february(second month)
                (
                 [Time].[Year].CURRENTMEMBER.FIRSTCHILD.LEAD(1).CHILDREN, 
                 10
                )
            ) ON COLUMNS
    FROM YourCube
)