How can I calculate Full Year in MDX in SSAS

Question

I am using Built in time intelligence feature and I would like to calculate measures for the Full Year.
Eg if current member of date is at 2015/03/01; I want to have a calculated measure from 2015/01/01 till 2015/12/31.

CREATE MEMBER CurrentCube.[DimTime].[FY-FQ-FM DimTime Calculations].[Full Year] AS "NA" ; 
( 
  [DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date]
, [DimTime].[Fiscal Year].[Fiscal Year].Members
, [DimTime].[Date].Members
, { [Measures].[Forecasts], [Measures].[Budget] } 
) 
= Aggregate( 
   { [DimTime].[FY-FQ-FM DimTime Calculations].[Current DimTime] } 
     * PeriodsToDate( 
         [DimTime].[FY-FQ-FM].[Fiscal Year]
       , [DimTime].[FY-FQ-FM].CurrentMember
       ) 
   ) ;

What does your date / time dimension structure look like? Do you have a user hierarchy within the dimension that is multi-leveled? — whytheq
do you want to add this calculation into the cube script or are you just trying to include it in an ad hoc mdx script? — whytheq
Thank your for you attention . My date dimension is regular SSAS time dimension including fiscal period hierarchy just like normal quarter calendar hierarchy. I would like Full Year calculation part of the cube calculation; for example like below: @whytheq — the beginner
Create Member CurrentCube.[DimTime].[FY-FQ-FM DimTime Calculations].[Full Year] As "NA" ; ( [DimTime].[FY-FQ-FM DimTime Calculations].[Year to Date], [DimTime].[Fiscal Year].[Fiscal Year].Members, [DimTime].[Date].Members, { [Measures].[Forecasts], [Measures].[Budget] } ) = Aggregate( { [DimTime].[FY-FQ-FM DimTime Calculations].[Current DimTime] } * PeriodsToDate( [DimTime].[FY-FQ-FM].[Fiscal Year], [DimTime].[FY-FQ-FM].CurrentMember ) ) ; — the beginner
@thebeginner, the above mentioned calculation is neither in a proper syntax of a cube calculation nor an ad hoc MDX query, could you please review it and put it in proper syntax? Please also explain what do you expect as the end result by using the Measures Forecasts and Budget. Do you expect an aggregated value starting from first day of current year to current date? — Akshay Rane

the beginner the beginner · Accepted Answer · 2015-05-19T07:09:44

Thanks @whytheq and @AkshayRane for the help. I was able to do the full year using below.

(
    [DimTime].[FY-FQ-FM DimTime Calculations].[Full Year],
    [DimTime].[Fiscal Year].[Fiscal Year].Members,
    [DimTime].[Date].Members,
    {
      [Measures].[Forecasts],
      [Measures].[Budget]
    }

)


=

  Aggregate(
             { [DimTime].[FY-FQ-FM DimTime Calculations].[Current DimTime] } 
             *

            PeriodsToDate(
                            [DimTime].[FY-FQ-FM].[Fiscal Year],
                           (
                            ANCESTOR(  
                                      [DimTime].[FY-FQ-FM].CURRENTMEMBER,
                                      [DimTime].[FY-FQ-FM].[Fiscal Year] 
                                    )
                           )
                        )
          )

How can I calculate Full Year in MDX in SSAS

1 Answers