SSAS Accumulated calculation

• Thursday, June 14, 2012 2:57 AM

   

   
  

  0

  It`s there are any way to do a cumulative sum from 2011-01-01 to 2012-06-13? 

  Date                 State  Task    Record Count

  2011-12-29  New       3  1

  --------------------------------------------------------

  2012-01-01      New      1  1
  2012-01-09      New      1        -1
  2012-01-09 Open     1          1
  2012-01-09 New      2  1
  2012-02-01  Open     1        -1
  2012-02-01  Closed   1          1

  Number of Task in state 'New' on 2012-01-08  should be 2  (Task 3 and Task 1)

  I was able to accumulate this value only per Year using:

  WITH MEMBER [Measures].[Total Count] AS
      SUM({null:[Time Dimension].[Year].CurrentMember},[Measures].[Record Count])    
  SELECT 
  {[Measures].[Record Count],[Measures].[Total Count]} ON 0,
      {NonEmpty([Time Dimension].[Year].Members) * NonEmpty([Issue Status].[Name].Members)} ON 1
  FROM [MyCube]

  Result-->

  Year        State      Accumulate

  2011        New            1
  2011        Open      (null)
  2011        Closed (null)
  2012        New            2
  2012        Open          0
  2012       Closed         1

  Any Ideas?

  
  

  • Edited by Panzu1979 Thursday, June 14, 2012 3:16 AM
  • Edited by Panzu1979 Thursday, June 14, 2012 3:18 AM
  •  
  • Reply
  • Quote