HI plz check the below query... --PRE
HI plz check the below query...
--PREVIOUS MONTH SALE
WITH
MEMBER
[Measures].PreviousMonthRevenue1 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],1,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue2 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],2,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue3 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],3,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue4 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],4,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue5 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],5,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue6 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],6,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue7 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],7,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue8 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],8,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue9 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],9,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue10 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],10,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
MEMBER
[Measures].PreviousMonthRevenue11 as
'SUM(PARALLELPERIOD([DimTime].[Year - Quarter - Month - Date].[Month],11,[DimTime].[Year - Quarter - Month - Date].CURRENTMEMBER),[Measures].[Revenue])'
SELECT
NON EMPTY{[Measures].[Revenue],[Measures].PreviousMonthRevenue1,[Measures].PreviousMonthRevenue2,[Measures].PreviousMonthRevenue3,[Measures].PreviousMonthRevenue4,[Measures].PreviousMonthRevenue5,[Measures].PreviousMonthRevenue6,[Measures].PreviousMonthRevenue7,[Measures].PreviousMonthRevenue8,[Measures].PreviousMonthRevenue9,[Measures].PreviousMonthRevenue10,[Measures].PreviousMonthRevenue11} ON 0,
DESCENDANTS([DimTime].[Year - Quarter - Month - Date].[Year].&[2011-01-01T00:00:00],[DimTime].[Year - Quarter - Month - Date].[Month]) ON 1
FROM
[FVSales]
SO FOR EACH MONTH I HAVE CREATED A CALCULATED MEMBER...IS THERE ANY WAY TO MAKE CALCULATED MEMBER DYNAMIC...
EX: IF I GIVE 3 AS THE PARAMETER(IN THE MDX REPORT) I SHOULD GET 3 PREVIOUS MONTH'S SALE.. PLZ SUGGEST