How to create a full year calculated measure mdx?

Question

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?

Best regards, Rubrix

vldmrrdjcc vldmrrdjcc · Accepted Answer · 2020-04-16T09:45:16

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]

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])

How to create a full year calculated measure mdx?

1 Answers