MDX Count over time (years - not within a year)

Question

I'd like to be able to rollup the count of commitments to a product over years -

The data for new commitments in each year looks like this:

Year    | Count of new commitments | (What I'd like - count of new commitments to date)  
1986    4        4  
1987    22       26  
1988    14       40  
1989    1        41  

I know that within a year you can do year to date, month to date etc, but I need to do it over multiple years.

the mdx that gives me the first 2 columns is (really simple - but I don't know where to go from here):

select [Measures].[Commitment Count] on 0
, [Date Dim].[CY Hierarchy].[Calendar Year] on 1
from [Cube]

Any help would be great

Answer 1

In MDX something along the line:

with member [x] as sum( 
    [Date Dim].[CY Hierarchy].[Calendar Year].members(0) : [Date Dim].[CY Hierarchy].currentMember,
    [Measures].[Commitment Count] 
)

select [x] on 0, [Date Dim].[CY Hierarchy].[Calendar Year] on 1 from [Cube]

Answer 2

Use a common table expression:

with sums (year,sumThisYear,cumulativeSum) 
as (
    select year
         , sum(commitments) as sumThisYear
         , sum(commitments) as cumulativeSum
      from theTable
     where year = (select min(year) from theTable)
     group by year
     union all
    select child.year
         , sum(child.commitments) as sumThisYear
         , sum(child.commitments) + parent.cumulativeSum as cumulativeSum
      from sums par
      JOIN thetable Child on par.year = child.year - 1
     group by child.year,parent.cumulativeSum
)
select * from sums

There's a bit of a "trick" in there grouping on parent.cumulativeSum. We know that this will be the same value for all rows, and we need to add it to sum(child.commitments), so we group on it so SQL Server will let us refer to it. That can probably be cleaned up to remove what might be called a "smell", but it will work.

Warning: 11:15pm where I am, written off the top of my head, may need a tweak or two.

EDIT: forgot the group by in the anchor clause, added that in