Answered by:
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.
Answers
-
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.
All replies
-
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.
-
-
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.