Search code examples
ssasmdxcubemeasure

How to create a full year calculated measure mdx?

Hello MDX and SSAS people,

I was wondering how to create a time calculation measure that summarizes the selected period/month's total value for the entire year in this case Budget. So if I select 202004 from my time hierarchy it should be the total budget of year 2020 and if I select 201904 it should be the total budget of 2019.

In the picture below I have created [YTD Budget] with the following mdx code:

Aggregate(periodstodate([Time].[Year-Month-Day].[Year],[Time].[Year-Month-Day].currentmember), 
[Measures].[Budget Amount])

Now I would like to create [Full year Budget] but have not found a solution yet (novice mdx user). Anyone that has a solution for this?

enter image description here

Best regards, Rubrix

Solution

  • Here is the example on Adventure Works:

    with member Measures.YearName 
AS (ANCESTOR([Date].[Calendar].Currentmember, [Date].[Calendar].[Calendar Year])).Name

member Measures.FullYear 
AS SUM(ANCESTOR([Date].[Calendar].Currentmember, [Date].[Calendar].[Calendar Year]), [Measures].[Internet Sales Amount])

select {[Measures].[Internet Sales Amount], Measures.FullYear, Measures.YearName } on 0,
{[Date].[Calendar].[Calendar Quarter].&[2012]&[1],
[Date].[Calendar].[Calendar Quarter].&[2012]&[2],
[Date].[Calendar].[Calendar Quarter].&[2012]&[3],
[Date].[Calendar].[Calendar Quarter].&[2012]&[4],
[Date].[Calendar].[Calendar Quarter].&[2013]&[1],
[Date].[Calendar].[Calendar Quarter].&[2013]&[2],
[Date].[Calendar].[Calendar Quarter].&[2013]&[3],
[Date].[Calendar].[Calendar Quarter].&[2013]&[4]
} on 1
from [Adventure Works]

    enter image description here

    So in your case the member definition should be:

       with member Measures.FullYear 
    AS SUM(ANCESTOR([Time].[Year-Month-Day].Currentmember, [Time].[Year-Month-Day].[Year]), [Measures].[Budget Amount])