using MDX in SSAS, retrieve data for current months along with previous last 12 months for comparison

Viewed 43

I have requirements to retrieve data for current months to date for current year along with last 12 month of last year.

For example if today is 15-Mar I want all data for current month and 1-15th of each months ( for previous year). I have used this query but it retrieve all days not 15th of each month:

WITH 
    MEMBER [Previous Days_Value] AS 
        Aggregate
        (
            MTD(),
            [Measures].[Lead Count]
        ) 
SELECT 
    NON EMPTY 
        [Previous Days_Value] ON 0,
    NON EMPTY 
        [Date].[Calendar].MEMBERS ON 1
FROM 
(
    SELECT 
        {
            [Date].[Calendar].[Year].&[2020],
            [Date].[Calendar].[Month].&[2021]&[1],
            [Date].[Calendar].[Month].&[2021]&[2],
            [Date].[Calendar].[Month].&[2021]&[3]
        } ON COLUMNS
    FROM [EDW]
);
0 Answers
Related