Hi,

I need to perform a calculation where I substract the value of my current member from the value of the previous member. However sometimes the previous member is empty, in which case, I want to use the first non empty previous member available.

This is a slimmed down copy of my query. I've been playing around with various functions like parallelperiod, prevmember, isempty etc... but haven't managed to work it out yet. Any help appreciated!


with

MEMBER [measures].[subscriptions previousMember] as
([measures].[subscriptions],parallelperiod([time].[time].[month],1,[time].[time].currentmember))

MEMBER [measures].[subscriptions change] as
'[measures].[subscriptions] - [measures].[subscriptions previousMember]'

SELECT non empty {[Time].[Time].[2008].[Q3].[sep]:[Time].[Time].[2013].[Q4].[dec]} on columns,
{[measures].[subscriptions],
[measures].[subscriptions previousMember],
[measures].[subscriptions change]} on rows
FROM [subscription facts]


Thanks