I am having the following data in my SSAS cube
I need to get the value based on a comparison between the current value and the previous value using MDX query.
In this example data, I need to get the Weight value as Result when the current value of Id column is not equal to the previous value of Id column otherwise 0.
Please find the formula and the sample result in the below screenshot for reference.
I have tried to achieve the requirement using PrevMember function available in the Mdx but it is not working.
Please find the query I have tried:
WITH MEMBER [Measures].[Expression1] AS IIF([Id].CURRENTMEMBER = [Id].CURRENTMEMBER.PrevMember,0,[Weight])
SELECT{ [Measures].[Expression1]} ON COLUMNS ,
NONEMPTY((TOPCOUNT( (Order((([class].[Id].[Id].ALLMEMBERS)),[class].[Id].CurrentMember.MemberValue,ASC)),101)),
{ [Measures].[Expression1]}) ON ROWS FROM [Model] CELL PROPERTIES VALUE, FORMATTED_VALUE, FORMAT_STRING
The result am getting for the query is
