Calculated measure for multiple date hierarchies

Question

I have a calculated measure which I am trying to get to work over multiple hierarchies (Year-Quarter-Month-Date) in my date dimension using scope.

The measure works fine on the Fiscal Hierarchy but not on the Calendar Hierarchy, once I remove the Month level the value returns the same for all values in the dimension.

CALCULATE;      
CREATE MEMBER CURRENTCUBE.[Measures].[Average Head Count]
 AS NULL, 
FORMAT_STRING = "Standard", 
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'Availability';
SCOPE([Measures].[Average Head Count]);
    -- Calendar Hieararchy
    SCOPE([Date].[Calendar Hierarchy].Members);
    THIS = Avg(Descendants([Date].[Calendar Hierarchy].CurrentMember, [Date].[Calendar Hierarchy].[Date]), [Measures].[Availability Count]);
    END SCOPE;

    -- Fiscal Hierarchy
    SCOPE([Date].[Fiscal Hierarchy].Members);
    THIS = Avg(Descendants([Date].[Fiscal Hierarchy].CurrentMember, [Date].[Fiscal Hierarchy].[Date]), [Measures].[Availability Count]);
    END SCOPE;
END SCOPE;

I can create them as two seperate measures using the same calculations as specified against THIS so something must in be incorrect with my scope syntax.

Tom Huang Tom Huang · Accepted Answer · 2017-06-14T23:48:45

The set in the scope include [Date].[Calendar Hierarchy].[ALL]/[Date].[Fiscal Hierarchy].[ALL], how about change your code like this:

SCOPE([Measures].[Average Head Count]);
-- Calendar Hieararchy
SCOPE([Date].[Calendar Hierarchy].[Calendar Hierarchy]);
THIS = Avg(Descendants([Date].[Calendar Hierarchy].CurrentMember, [Date].[Calendar Hierarchy].[Date]), [Measures].[Availability Count]);
END SCOPE;

-- Fiscal Hierarchy
SCOPE([Date].[Fiscal Hierarchy].[Fiscal Hierarchy]);
THIS = Avg(Descendants([Date].[Fiscal Hierarchy].CurrentMember, [Date].[Fiscal Hierarchy].[Date]), [Measures].[Availability Count]);
END SCOPE;
END SCOPE;

Calculated measure for multiple date hierarchies

1 Answers