aggregate measure over time

Question

I have a measure in my fact table that I would like to aggregate from the beginning of time (in my time dimension, not the big bang) to the current time, whatever that might be.  If I was running a report by month, I need total from 0 - Jan, then 0 - Feb, 0 - Mar, etc.

 

Does anyone know the MDX function that would do this - either create a calculated measure in my cube or use it in a report data set?

 

Thanks.

 

PS.

 

I tried a calculated measure as:

sum([Date Received].[Date].[Date].members(0):ClosingPeriod([Date Received].[Date].[Date]),[Measures].[Add Remove])

 

It takes about 5 minutes to populate the table in VS Browser.  It starts at the first date I ever added inventory, but repeats a lot of dates that should be blank.

 

06/05/2006     1

06/06/2006     1

06/07/2006     1

06/08/2006     1

06/09/2006     2

06/10/2006     2

06/11/2006     2

06/12/2006     3

06/13/2006     3

 

It eventualy gets it correct but when I try to add the calucalte measure to a data set in an SSRS report, it goes into a loop so long that I had to shut the program down with task manager.

Answer 1

I guess the problem with this one was that it was too easy.  After messing around with nonempty and non empty I finally figured out all I had to do was set the Non-empty behavior drop down box.  D'OH!

 

I'm still not sure if that translates as non empty or nonempty, perhaps when I get the time I'll investigate further.

 

 

 

Answer 2

This is nearly exactly what I've been searching for, thanks!

 

How would I modify this so that it's not tied directly to a specific time dimension attribute, but is able to be used regardless of the time heirarchy or specific attribute chosen by the user?

Answer 3

Nevermind.  I got it.  The trick was to switch to using the heirarchy, specifying the proper granularity attribute.

 

sum([Membership Start Date].[Year - Quarter - Month - Date].[Date].Members(0):ClosingPeriod([Membership Start Date].[Year - Quarter - Month - Date].[Date]),[Measures].[Count of Memberships])

 

This is a wonderful solution, thank you, thank you!  personally, I believe that this should be a built-in "Running Totals" aggregate function which should be point-n-click to add to any measure.