i have been doing a query to get previous year value in MDX but can't the the desired output. In sql i would do it like this. Question is how do i apply this query in MDX?

select data1, sum(data2) as sumthisyear, sumlastyear from table1 a left outer join ( select data1, sum(data2) 'sumlastyear' from table1 where tableyear = 2017 group by data1 ) b on a.data1 = b.data1 where tableyear= '2018' and datafilter = 'SampleFilter' group by data1, sumlastyear

SSAS Cube Structure Image

I have done some research and did mdx function like parallelperiod but not giving the correct values as data are filtered based on 2018 year.

Here's my MDX query

    iif( [DimProdDate].[Dates].CurrentMember Is

         ( PARALLELPERIOD( [DimProdDate].[Dates].[Calendar Year],
                           Tail( NonEmpty( [DimProdDate].[Dates].[Full Date].MEMBERS,
                                           [MEASURES].[Registered Sales]),
           [MEASURES].[Registered Sales] ),

         ( PARALLELPERIOD( [DimProdDate].[Dates].[Calendar Year],
           [MEASURES].[Registered Sales] )

Any help will be very much appreciated.


Please can you add the MDX which you mention that you’ve triedwhytheq
Hi whytheq thank you for your reply. I edited may question to include my mdx query as well as my cube structure.Kiddo Gabaldon
The tricky part here is that, that output that i'd like to get was the total for this year with filtered by BaseType but the total for last year should reflect without BaseType filter. The reason behind is that Company A for example was BaseType for this year but against the previous period Company A was a Non-Base. To make it short - Show Base type for this year vis-a-vis last year regardless if base or non-base. Thanks!Kiddo Gabaldon