Finding the first non empty previous member

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

Excellent questions!

"Non-empty previous member" can be generated in multiple ways.... In virtually all, you simply need to get the "last populated member" first, and then apply ParallelPeriod, etc., to that, to have AS look back to the previous year, etc.

Fundamentally, using the ClosingPeriod() function might be a good bet to get the "last period with activity," etc. Something along the lines of the following pseudocode (to be adjusted to fit your structure, of course):

(ClosingPeriod( [Month], [Time].CurrentMember), [Measures].[Quantity])

Once you give the function the level / member info in which you are interested, it will return the last member found at the specified level .

This is a shortcut, of course, for the Tail() function, which if used in a similar scenario as that above would look something like this (again, pseudocode):

(Tail( Descendants( [Time].CurrentMember, [Month]), 1).Item(0), [Measures].[Quantity])

To see some of this in action, check out my Database Journal article at the following link:

Support Parameterization from Analysis Services – Parameter Defaults

With one of the better examples I show on this page specifically:

http://www.databasejournal.com/featu...r-Defaults.htm

The examples I show in the article use MDX in calculated members, which I then use to support parameter defaults in Reporting Services. Most deal with “last period with sales” sorts of things, to allow parameter picklists at report run time to default only to the “latest period with activity,” etc. I'm simply applying the logic we're discussing to a practical use in RS, but you can ignore the parts that are not relevant to your needs.

Give this a shot, and get back with any specific questions.

Thanks.

Bill

Thanks Bill,

We worked it out in the end using a nice little recursive call as below. We were also looking at the LastNonEmpty semi-additive measure however I didn't want to create an additional measure in the cube in addition to my current measure which already has sum specified for it's aggregation.

member [measures].[lastnonempty] as
case when not isempty(([measures].[price], [time].[time].prevmember))
then ([measures].[price], [time].[time].prevmember)
else [time].[time].prevmember
end

Regards, Jon

Many thanks for sharing your findings - as I often say in these sorts of scenarios, your "closing the case," with the input of a solution that worked for you, makes the entire thread highly valuable as a freestanding reference. When we, as correspondents, complete entries, begun as real-world problem statements, with useful solutions like this, it creates a valuable reference that others can locate, via searches, when they encounter similar needs.

It's people like you that make it all work!

Bill