title: SSAS Opening Balance
body: <p>I am building a cube in SSAS that uses inventory movement data to show item activity.  A sample of this data can be seen here (hopefully this is readable).</p>
  
  <p>Date = Customer =   Item = Receive Qty   = Ship Qty = Movement</p>
  
  <p>1/1/2000 = Cust123   = Item123 = 100 = 0 =  100</p>
  
  <p>11/5/2011 = Cust123 = Item123 = 10 = 0 = 10</p>
  
  <p>11/6/2011    = Cust123 =    Item123 =   0 =     -5 =    -5</p>
  
  <p>11/7/2011    = Cust123 =    Item123 =   1 =     0 =     1</p>
  
  <p>The goal is to include an opening balance in the cube.  The opening balance for a given date is defined as the sum of all Movement (from the Movement field) for a given customer and item (my fact table contains many different customers and items) for all dates prior to the current date.  </p>
  
  <p>So in the sample data, the opening balance for the 11/6/2011 date would be the sum of all Movement (from the Movement field) where the customer and item are a match, and the date is less than 11/6/2011.  Which would go all the way back to 1/1/2000 in my sample data.</p>
  
  <p>Is there a way that MDX could help me accomplish this in my cube?  I am relatively new to SSAS, but I believe there should be a way for me to include some MDX in the Calculations tab correct?  Any help would be greatly appreciated.  Thanks.</p>