Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. INF: How to Calculate the Cumulative Value of a Measure

INF: How to Calculate the Cumulative Value of a Measure

Summary

In a number of applications and business reports you want to obtain or return cumulative values for one or more measures. This article illustrates the use of the SUM, PeriodsToDate, YTD, QTD, and ITEM functions to return cumulative values over a period of time.

↑ Back to the top

More Information

One approach to calculating cumulative values for a measure during all time requires the addition of an (All) level to the time dimension:

  1. Open Analysis Manager, and then open the FoodMart 2000 database.
  2. Edit the Time dimension and add an All level.
  3. Process the Time dimension and the Sales cube. Adding the All level results in members being summed automatically.
  4. Open the MDX sample application, and then select the FoodMart 2000 sample database.
  5. Test the validity of the formula that you will be using for a calculated measure by executing this Multidimensional Expressions (MDX) query:
    WITH MEMBER MEASURES.[Sales To Date] AS 'Sum(PeriodsToDate([Time].[(All)]), [Measures].[Unit Sales])'
    SELECT
       {[Measures].[Unit Sales],[Measures].[Sales To Date]} ON COLUMNS, 
       [Time].[Month].Members ON ROWS
    FROM [Sales]
  6. Note the values that display in the data grid.
    MonthUnit SalesSales to Date
    121,628.0021,628.00
    220,957.0042,585.00
    323,706.0066,291.00
    420,179.0086,470.00
    521,081.00107,550.00
    621,350.00128,901.00
    723,763.00152,664.00
    821,697.00174,361.00
    920,388.00194,749.00
    1019,958.00214,707.00
    1125,270.00239,977.00
    1226,796.00266,773.00


  7. Close the MDX sample application.
  8. Edit the Time dimension and remove the All level. Re-process the Time dimension and the Sales cube.
An alternate approach to calculating cumulative values for a measure requires use of the ITEM function with the colon operator to sum over a range of times.

  1. Open the MDX sample application, and then select the FoodMart 2000 database.
  2. Test the validity of the formulas that you are going to use to calculate measures by executing this MDX query:
    WITH 
     MEMBER MEASURES.[Sales To Date] as 'SUM({[Time].CurrentMember.Level.Members}.Item(0):Time.CurrentMember,[Measures].[Unit Sales])'
      -- Use the ITEM() function to return the first member then sum to the currentmember
     MEMBER MEASURES.[Sales Year To Date] AS 'SUM(YTD(),[Measures].[Unit Sales])'
      -- Use the YTD() function to sum for Year To Date
     MEMBER MEASURES.[Sales Quarter To Date] as 'SUM(QTD(),[Measures].[Unit Sales])'
      -- Use the QTD() function to sum for Quarter To Date
    SELECT
       {[Measures].[Unit Sales],[Measures].[Sales To Date],[Measures].[Sales Year To Date],[Measures].[Sales Quarter To Date]} ON COLUMNS, 
       [Time].[Month].Members ON ROWS
    FROM [Sales]
  3. Close the MDX sample application.
  4. Edit the Time dimension and change the All level to NO. Re-process the dimension and the Sales cube.
Now that you have tested the formulas for the calculated members, you can add the formulas to the Sales cube.

  1. Open the Sales cube for editing.
  2. Add a new calculated member with the following properties: 

    Parent Dimension: Measures
    Name            : Sales To Date
    Value           : SUM({[Time].CurrentMember.Level.Members}.Item(0):Time.CurrentMember,[Measures].[Unit Sales])
    Format String   : STANDARD
    Click OK to save the calculated member. This calculated member returns the value of the Unit Sales measure since the beginning of time.
  3. Add a new calculated member with the following properties: 

    Parent Dimension: Measures
    Name            : Sales Year To Date
    Value           : SUM(YTD(),[Measures].[Unit Sales])
    Format String   : STANDARD
    Click OK to save the calculated member. This calculated member returns the value of the Unit Sales measure for the year to date.
  4. Click OK to save the calculated member. This calculated member returns the value of the Unit Sales measure for the quarter to date.
    Parent Dimension: Measures
    Name            : Sales Quarter To Date
    Value           : SUM(QTD(),[Measures].[Unit Sales])
    Format String   : STANDARD
  5. Browse the cube data and note the values that display in the newly added calculated members.
    MonthUnit SalesSales to DateSales Year to DateSales Quarter to Date
    121,628.0021,628.0021,628.0021,628.00
    220,957.0042,585.0042,585.0042,585.00
    323,706.0066,291.0066,291.0066,291.00
    420,179.0086,470.0086,470.0020,179.00
    521,081.00107,550.00107.550.0041,260.00
    621,350.00128,901.00128.901.0062,610.00
    723,763.00152,664.00152,664.0023,763.00
    821,697.00174,361.00174.361.0045,460.00
    920,388.00194,749.00194,749.0065,848.00
    1019,958.00214,707.00214,707.0019,958.00
    1125,270.00239,977.00239,977.0045,228.00
    1226,796.00266,773.00266,773.0072,024.00

↑ Back to the top

Keywords: kb, kbinfo, kbdsupport, kbbillprodsweep

↑ Back to the top

Article Info
Article ID : 301637
Revision : 6
Created on : 8/19/2020
Published on : 8/20/2020
Exists online : False
Views : 143