SSAS new calculated member to display summary info

Question

I am facing an issue that looks trivial to me but that cannot solve for days now... My cube has mainly one DimDate dimension (to simplify my issue): in the dim date, I have two hierarchies: one standard (Time) that decompose the date in Date, Month, Quarter and Year One (Trimester Time) that decompose the date in Date, Month, Trimester (one third of a year) and Year. on my fact table I have for each day a purchase Orders (PO) and a measure integer (0 or 1) that indicate that the PO is active. When browsing on each date I am able to see how much PO are active (Sum).

As you can expect, when I am looking at the figures for the month... I have the sum for each day... which is obviously not what I am expecting. I am trying to find that number that where open on the very last day of that period.

I then created several attributes to the dimDate: Is Last Day Of Calendar Qtr Is Last Day Of Calendar Trimester Is Last Day Of Calendar Month Is Last Day Of Calendar Year

Then I created a new calculated member that is like that:

    CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasures]
 AS CASE
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarYear] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y],[Dim Date].[Month Of Year].&[12])
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarQtr] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Calendar Qtr].&[Y])
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarMonth] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y])
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[Date] then
        [Measures].[Active]
    ELSE NULL
END, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Duration';   

this was working fine up until I got requested to extend the measure for both hierarchies. I then did update the member with the following:

    CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasures]
 AS CASE
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarYear] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y],[Dim Date].[Month Of Year].&[12])
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarQtr] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Calendar Qtr].&[Y])
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarMonth] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y])
    WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[Date] then
        [Measures].[Active]

    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[CalendarYear] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y],[Dim Date].[Month Of Year].&[12])
    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[Calendar Year Trimester] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Calendar Trimester].&[Y])
    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[CalendarMonth] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y])
    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[Date] then
        [Measures].[Active]

    ELSE NULL
END, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Duration';   

their must be something that is conceptualy wrong in my though process, but I cannot get it... can someone help me?

as a result I get the bellow results in Excel:

with the time hierarchy, it works fine

with the trimester hierarchy, it shows only 3rd trimester...

Answer 1

Are these the only levels in [Dim Date].[Time]?

[Dim Date].[Time].[CalendarYear]
[Dim Date].[Time].[CalendarQtr]
[Dim Date].[Time].[CalendarMonth] 
[Dim Date].[Time].[Date]

If they are all the levels of the hierarchy then your CASE statement will never get to the new options you've added because one of the above 4 will always be true.

Answer 2

@whytheQ has identified a problem in the last comment: because you're trying to cater for two hierarchies, you need to react to the situation where one (or both) of the hierarchies are "not in use". If both hierarchies are "in use", all bets are off - I'm not sure I'd even try to cater for that situation; probably I'd just "give up" and fall back to a NULL value.

How about trying this?

CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasures]
 AS

 -- Is Time hierarchy current member "ALL" level?
 CASE WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].Levels(0)
 THEN
    -- Not interested in Time hierarchy - see if we need to do anything interesting on Time Trimester hierarchy
    CASE 
    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[CalendarYear] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y],[Dim Date].[Month Of Year].&[12])
    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[Calendar Year Trimester] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Calendar Trimester].&[Y])
    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[CalendarMonth] then
        ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y])
    WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[Date] then
        [Measures].[Active]
    ELSE
        -- Time Trimester All - neither hierarchy in use: maybe show measure from last date ever here, instead of NULL?
        NULL
    END
ELSE
     CASE
        WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarYear] then
            ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y],[Dim Date].[Month Of Year].&[12])
        WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarQtr] then
            ([Measures].[Active],[Dim Date].[Is Last Day Of Calendar Qtr].&[Y])
        WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarMonth] then
            ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y])
        WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[Date] then
            [Measures].[Active]
        -- No need for ELSE here if all levels of [Time] are covered
    END
END
, 
VISIBLE = 1 ,  DISPLAY_FOLDER = 'Duration'; 

You might get better performance (or more clarity) by using SCOPE to only invoke this logic if the .CurrentMember in either [Time] or [Time Trimester] hierarchy is NOT All or the leaf (Date) level.

Answer 3

I solved my issue by using the bellow statement:

CASE
    WHEN [Dim Date].[Time].currentMember.Level.Ordinal=0 THEN 
        NULL
    WHEN [Dim Date].[Time Trimester].currentMember.Level.Ordinal<>0 THEN 

        CASE WHEN [Dim Date].[Time].currentMember.Level is [Dim Date].[Time].[CalendarQtr] then
                ([Measures].[Active],[Dim Date].[Is Last Day Of Calendar Qtr].&[Y])
             WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[Calendar Year Trimester] then
                ([Measures].[Active],[Dim Date].[Is Last Day Of Calendar Trimester].&[Y])

             WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[CalendarYear] then
                iif(isempty(([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y],[Dim Date].[Month Of Year].&[12])),1,([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y],[Dim Date].[Month Of Year].&[12]))
             WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[CalendarMonth] then
                ([Measures].[Active],[Dim Date].[Is Last Day Of Month].&[Y])
             WHEN [Dim Date].[Time Trimester].currentMember.Level is [Dim Date].[Time Trimester].[Date] then
                [Measures].[Active]
         END

    ELSE NULL
    END

I must admit that I am not yet happy with the final statement but at least this issue is solved....

O