Search code examples
ssascube

How to calculate a YTD based on the Month short


I have hierarchy: Month,Monthshort and Year-Quarter-Month under TimeDim Dimension,a scope is available like below to calculate YearToDate(YTD) Scope([TimeDim].[Year - Quarter - Month].[Month].members, [TimeDim].[Month].Members);

YTD:

([TimeDim].[YTD]=   
          Sum(                
        CrossJoin(
            {[Time Calculation].[Current Period]},                  
            PeriodsToDate([TimeDim].[Year - Quarter - Month].[Year],                  
                [Timedim].[Year - Quarter - Month].CurrentMember
            )
        )
    )
);  

Month will be in format YYYY-MM fullname(eg: 2017-10 October) and Month short will be MMM(Oct). If I calculate YTD on the measure based on month it works fine, How to calculate YTD based on Month Short? Do I require to create new scope to calulate YTD based on Month short? I am new to SSAS. Please help


Solution

  • I created below scope for YTD with month short. Thus solved the problem.

    Scope([Time Year Month].[Year].members,[Time Year Month].[Year - Month].[Month Short].members);          
    -- accumulated YTD CALCULATIONS 
      ([Time Calculation].[YTD]=         
            SUM(                
                CrossJoin(
                    {[Time Calculation].[Current Period]},                  
                    PeriodsToDate([Time Year Month].[Year - Month].[Year]
                    )
                )
            )
    
        ); 
    End Scope;