How to view the first and/or last member values in a hierarchy

Question

Is there a way for me to view the first and/or last leaf value in a level of a hierarchy?

I am trying to create a calculation dimension in SSAS which will include, for example, a year to date calculation which I would prefer not to display for dates in the future.

I've worked out how to make that happen at the lowest level (date), but am getting errors ath te aggregation levels when trying to implement the technique.

To help me accomplish what I want I've included a [Date In Past] member in my dimension, which contains a 0 if the date is in the past, and a if it is not.

For example this query, which returns calculations by date:

with member [Measures].[Year To Date] as
            Sum(
                 { IIF(strtovalue(
                        [Time Order Date].[Date In Past].Currentmember.membervalue
                                    ) = 0, null, [Measures].[Product Rev (with ship, no disc)]
                        ) } *
                   PeriodsToDate(
                                [Time Order Date].[Fiscal Date].[Fiscal Year Name],
                                [Time Order Date].[Fiscal Date].CurrentMember
                                 ) 
                )
select
{[Measures].[Product Rev (with ship, no disc)],
[Measures].[Year To Date]} on 0,
[Time Order Date].[Date].Children on 1
from [Sales Analysis]

returns nulls in the [Year to Date] measure for all dates in the future.

This query, which returns calculations by the week:

with member [Measures].[Year To Date] as
            Sum(
                 { IIF(strtovalue(
                        [Time Order Date].[Date In Past].Currentmember.membervalue
                                    ) = 0, null, [Measures].[Product Rev (with ship, no disc)]
                        ) } *
                   PeriodsToDate(
                                [Time Order Date].[Fiscal Date].[Fiscal Year Name],
                                [Time Order Date].[Fiscal Date].CurrentMember
                                 ) 
                )
select
{[Measures].[Product Rev (with ship, no disc)],
[Measures].[Year To Date]} on 0,
[Time Order Date].[Fiscal Week Name].Children on 1
from [Sales Analysis]

returns errors for all of the [Year To Date] values, I assume because there are more than one member in the week.

I would like to compare it with the last day of the week. How can I do that?

Thanks, --sw

FrankPl FrankPl · Accepted Answer · 2014-11-18T08:21:40

To answer your question literally: Yes, you can get the first or last member of a set, and hence of a level, using the Head and Tail methods. Just note that these return a one-element set, hence you would often use Tail(something).Item(0).Item(0) to get a member.

However, as I understand your question, what you really need is to know if in the currently context, the member [Time Order Date].[Date In Past].[a] exists. In that case, I would use

with member [Measures].[Year To Date] as
            IIf(Intersect(EXISTING [Time Order Date].[Date In Past].[Date In Past].Members,
                          {[Time Order Date].[Date In Past].[a]}
                         ).Count = 1,
                NULL,
                Sum(PeriodsToDate([Time Order Date].[Fiscal Date].[Fiscal Year Name],
                                  [Time Order Date].[Fiscal Date].CurrentMember
                                 )
                    [Measures].[Product Rev (with ship, no disc)]
                   )
               )
select
{[Measures].[Product Rev (with ship, no disc)],
[Measures].[Year To Date]} on 0,
[Time Order Date].[Fiscal Week Name].Children on 1
from [Sales Analysis]

EXISTING gets the set of all Date In Past members that exist in the current context. And the intersection of that with the one-element set of member a has either 1 element (if a is member of the first set), or 0 elements (in case a is not contained in the set), which explains the condition for the IIf.

How to view the first and/or last member values in a hierarchy

1 Answers