SSAS MDX PrevMember within calculated measure not working

Question

Please could you assist. I am using the following expression to obtain the inflow for the same week for the previous month.

([Time].[Week Commencing Date].CurrentMember.PrevMember, [Measures].[_Inflow])

This appears to be returning results, however the grand totals are missing as this would be summing the weeks and assigning the value into a Month Year Total.

If i select Month Year in my time dimension, i get no results.

Could you please show me an example of how i can correct the expression.

I have tried [Time].[Time].CurrentMember.PrevMember, [Measures].[_Inflow] so that it uses the Time hierarchy but this does not work.

Time Dimension - Attribute: Month Year, Quarter, Week Commencing Date - Time(Hierarchy): Year, Quarter, Month Year, Week Commencing Date

Any help would be appreciated. Thank you.

MoazRub MoazRub · Accepted Answer · 2019-01-29T22:39:22

You need to use Cousin function .Take a look at the example below, I dont have week in the sample cube at hand in the hierarchy therefore I am using Date. In this example I am trying to get the same date for the previous month.

select
{[Date].[Calendar].[Date].&[20130922],
cousin([Date].[Calendar].[Date].&[20130922],[Date].[Calendar].[Month].&[2013]&[8])
}
on columns,
[Measures].[Internet Sales Amount]
on rows from 
[adventure works]

The Date UserHierarchy is Year- Semester- Quater- Month- Date

EDIT: Use Cousin as calculated member

To use the cousin function as calculated member you need to do the following. But make sure, that you have the date part used in the query.

select
{[Date].[Calendar].[Date].&[20130922],
cousin([Date].[Calendar].[Date].&[20130922],[Date].[Calendar].[Month].&[2013]&[8])
}
on columns,
[Measures].[Internet Sales Amount]
on rows from 
[adventure works]

SSAS MDX PrevMember within calculated measure not working

1 Answers