I am writing LYQTD and LYYTD measures mdx query for show previous year data on the basis of current selected date filter these mdx working quite fine but i am not able to aggregate Last Year to date and Last Quarter to date data. for ex:- I want to filter data for LYQTD and LYYTD.
these are some sales sample data for different months:
Jan 1000
Feb 5000
Mar 1000 Quarter1
apr 2000
May 4000
Jun 6000 Quarter2
Jly 8000
Aug 6000
Sep 4500 Quarter3
Oct 6500
Nov 9000
Dec 2000 Quarter4
Now i want to aggregate these data on the basis of selected filter for example i am select Jan month
LYQTD LYYTD
1000 1000
Now i am selecting Feb month the measures will be aggregated with the previous select filter
LYQTD LYYTD
6000 6000
Now i am selecting Quarter2 month apr the data will be shows as
LYQTD LYYTD
2000 8000
My applied queries as mentioned below if there is any solution please help me.
CREATE MEMBER CURRENTCUBE.[Measures].ActualsLYQTD
AS
AGGREGATE(PARALLELPERIOD([Time].[Time].[Quarter],4,[Time].[Time].CURRENTMEMBER),
[Measures].[Revenue and Expenses]),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Actuals' ;
CREATE MEMBER CURRENTCUBE.[Measures].ActualsLYYTD
AS
AGGREGATE(PARALLELPERIOD([Time].[Time].[Year],1,[Time].[Time].CURRENTMEMBER),
[Measures].[Revenue and Expenses]),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Ac``tuals' ;