Several Weeks ago

I want to make calculated member to filter transitions for example 5 weeks ago

([Measures].[Transitions to status],[Time.Weekly].[Week].CurrentDateMember.PrevMember) - it works with last week

([Measures].[Transitions to status],Filter([Time.Weekly].[Week].Members,
DateBetween([Time].CurrentHierarchyMember.StartDate,‘5 weeks ago’,‘4 weeks ago’)))
tried like this, what am i doing wrong ?


The problem with your proposed formula is that the Filter function returns set. The tuple in MDX requires a single member, and set will not work in this formula even if the set by logic can have only one member in it. The simplest adjustment would be to take the first element of this set:

([Measures].[Transitions to status],
    DateBetween([Time].CurrentHierarchyMember.StartDate,"5 weeks ago","4 weeks ago")

There is another solution for the same use case which is more optimal

([Measures].[Transitions to status],[Time.Weekly].[Week].CurrentDateMember.Lag(4))

This solution uses the Lag function which accesses the week member directly and does not need to iterate over a set of weeks and filter one of it.

Janis, eazyBI support