Pages

Sunday, November 30, 2014

SSAS [Period Utility] calculations + Accumulated (Addictive) figures

The following calculated members return accumulated figures for a given measure and selected level in period dimension (you need to have a period (time) utility dimension in the cube in order to create this member).
YTD (Year To Date Calculation)
CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[YTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;    
SCOPE ([Period Utility].[Period Accumulations].[YTD (Operational)]);  
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Year],
                   [Period].[Operational Year - Quarter - Month].CurrentMember                   
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );  
END SCOPE;  
Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg - Measure Name]} * {[Period Utility].[Period Accumulations].[YTD (Operational)]}

On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]

 









QTD (Quater To Date Calculation)

CREATE MEMBER CURRENTCUBE.[Period Utility].[Period Accumulations].[QTD (Operational)]
AS NULL,
FORMAT_STRING = "#,#.0",
VISIBLE = 1;    
SCOPE ([Period Utility].[Period Accumulations].[QTD (Operational)]);  
    this = SUM(
               PeriodsToDate(
                   [Period].[Operational Year - Quarter - Month].[Quarter],
                   [Period].[Operational Year - Quarter - Month].CurrentMember                   
               ), ([Measures].CurrentMember, [Period Utility].[Period Accumulations].DefaultMember)
           );  
END SCOPE;   


Following MDX can be used to validate figures.
SELECT
(
{[Measures].[Stg -
Measure Name]} * {[Period Utility].[Period Accumulations].[QTD (Operational)]}

On Columns,
Non Empty {
[Period].[Operational Year - Quarter - Month].[Month].members
} On Rows
From [Cube Name]