none
SSAS Accumulated calculation

    Question

  • 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:18 AM
    Thursday, June 14, 2012 2:57 AM

All replies

  • Hi,

    SUM(YTD(DateHierarchy), Measure.[MyMeasure]) should work.

    HTH

    Thomas Ivarsson

    Thursday, June 14, 2012 5:44 AM
    Moderator
  • Thomas YTD it doesn't work.

    YTD gives me data from (2012-01-01) to current date, and It doesn't take into account data in previous years

    • Edited by Panzu1979 Thursday, June 14, 2012 1:11 PM
    Thursday, June 14, 2012 12:54 PM