Search code examples
ssasmdx

[Date].[Calendar Year].CURRENTMEMBER.CHILDREN not picking up children

If I run the following the two custom measures return the same results. Why doesn't [Measures].[AvgOfChildren] return the average for the level below [Calendar Year] ? 

WITH MEMBER [Measures].[Avg Yearly Reseller Sales Amount] AS
    AVG(
        {[Date].[Calendar].[Calendar Year].MEMBERS},
        [Measures].[Reseller Sales Amount])
MEMBER [Measures].[AvgOfChildren] AS
    AVG(
        {[Date].[Calendar Year].CURRENTMEMBER.CHILDREN}, //[Date].[Calendar].[Month].MEMBERS
        [Measures].[Reseller Sales Amount])
SELECT
    {[Measures].[Avg Yearly Reseller Sales Amount],
    [Measures].[AvgOfChildren]} ON 0,
    {[Geography].[Geography].[Country].MEMBERS} ON 1
FROM [Adventure Works]
Solution

  • Your statement does not contain any setting for the [Date].[Calendar Year] hierarchy, neither in a WHERE condition, nor in the rows. And this means that the CurrentMember has the default value, which is the ``All member, named All Periods in this case.

    You can verify this by adding another calculated member

    Measures.CurrentYear as [Date].[Calendar Year].CURRENTMEMBER.NAME

    and adding that to your columns.