I am not able to sum LYQTD and LYYTD measures data

Question

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' ;

It's unclear what's wrong here to me, could you please provide more visual representations? — Danylo Korostil
Hi I have requirement, Previous Year YTD till the same date as YTD is returing results for example if the Records in my table is present from 1-jan-2016 to 9-May-2016 then this year YTD will Calculate till 9-May-2016 (this is Working fine ) and Previous year YTD also should calculate till 9-May-2015 from 1-Jan-2015 not for whole year or whole month.my mdx for LYQTD and LYYTD are mentioned above. — Manish Sharma
You have to use the ParallelPeriod() function in order to get the same member with parallel parents. — Danylo Korostil
Hi nice to reply ,But as you see on above query i have already used ParallelPeriod() function in ActualsLYQTD and ActualsLYYTD measures but i am not able to add selected months data. — Manish Sharma

Danylo Korostil Danylo Korostil · Accepted Answer · 2017-04-05T05:50:24

You have to use ParallelPeriod() to get the same member a year before, PeriodToDate() to get a set from the beginning of the year until current member, Aggregate() to get aggregated value of [Measures].[Revenue and Expenses] against the set.

Aggregate(
    PeriodToDate(
        [Time].[Time].[Year],
        ParallelPeriod(
            [Time].[Time].[Year],
            1,
            [Time].[Time].CurrentMember
        ),
    ), 
    [Measures].[Revenue and Expenses]
)

I am not able to sum LYQTD and LYYTD measures data

1 Answers