InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:20:30 GMT20http://www.sqlservercentral.com/Forums/Topic1395031-17-1.aspxHi Mack ,I'm on a roll...even simpler...using the "null" value brings back the first member of a set on the same level as the current member...only works if you use the range : (colon)-- Previous 3 month sales using tail and "null"WITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM(Tail(null:[Order Date].[CalendarHierarchy].CurrentMember,3), [Measures].[Sales Amount])'SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0, [Order Date].[CalendarHierarchy].[Month] On 1FROM MyFirstCube CheersRobinTue, 11 Dec 2012 06:43:00 GMTrobinrai3http://www.sqlservercentral.com/Forums/Topic1395031-17-1.aspxHi Mack,Your a diamond among the gems....fixed it...using your reply...gone back and determined the first child and created the set...and then did a tail on it....-- Previous 3 month sales using tailWITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM(Tail([Order Date].[CalendarHierarchy].CurrentMember.Parent.Children(0):[Order Date].[CalendarHierarchy].CurrentMember,3), [Measures].[Sales Amount])'SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0, [Order Date].[CalendarHierarchy].[Month] On 1FROM MyFirstCube Tue, 11 Dec 2012 05:19:53 GMTrobinrai3http://www.sqlservercentral.com/Forums/Topic1395031-17-1.aspxYou need to provide the Tail function with a set - you have provided the current member which is just one value ie a set with one valueSo Tail(CurrentMember,3) = CurrentMember. An example I use in my cubes (to get the latest year with revenue)TAIL(NONEMPTY({[Date].[Year].Children},{[Measures].[Net Revenue]}),1);MackTue, 11 Dec 2012 04:35:21 GMTMackershttp://www.sqlservercentral.com/Forums/Topic1395031-17-1.aspxHi All mdx gurus,I was checking out the tail function to achieve same the rolling 3 month sales which works using the lag function...as follows :-- Previous 3 month salesWITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM( [Order Date].[CalendarHierarchy].CurrentMember : [Order Date].[CalendarHierarchy].CurrentMember.Lag(2), [Measures].[Sales Amount])'SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0, [Order Date].[CalendarHierarchy].[Month] On 1FROM MyFirstCubethis does not however using tail substitution: -- Previous 3 month sales using tailWITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM(Tail([Order Date].[CalendarHierarchy].CurrentMember,3), [Measures].[Sales Amount])'SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0, [Order Date].[CalendarHierarchy].[Month] On 1FROM MyFirstCube all i get is the same value for the current member Question ? Can the tail function not create dynamic sets on currentmemberTue, 11 Dec 2012 04:28:37 GMTrobinrai3