none
aggregate measure over time RRS feed

  • 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.

    Friday, April 18, 2008 12:19 PM

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.

     

     

     

    Monday, April 21, 2008 8:10 PM

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.

     

     

     

    Monday, April 21, 2008 8:10 PM
  • 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?

    Friday, August 15, 2008 2:47 AM
  • 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.

    Friday, August 15, 2008 3:29 AM