Semi-additive measures problem - sum snapshots for each month, but not across months

Question

Proficient with SQL but new to MDX, I am having trouble getting my head around this:

I have a fact table that contains snapshots of account balances monthly. I need to roll up the balances as a semi-additive measure - straight sum doesn't work, obviously, for balances. However, I DO need to sum all the balances within EACH month, separately, by adding together the balances for all accounts, and so the "lastnonempty" notion isn't working for me either. Example, if the facts look like this:

Date        AccountNo     Balance
2009-01-31  1111          $100
2009-01-31  2222          $100
2009-01-31  4444          $100
2009-01-31  5555          $100

2009-02-28  1111          $100
2009-02-28  2222          $200
2009-02-28  3333          $500
2009-02-28  5555          $50

etc.

And I have an account dimension that groups accounts into a major category / minor category / account hierarchy, I need output like this that sums the balances for each month across all accounts:

Month        Total
January 09   $400
February 09  $850

And by broken out by account type:

Month        Total
January 09   $400
  Type 1      $200
    1111       $100
    2222       $100
  Type 2     $200
February 09  $850
  Type 1     $300
  Type 2     $550

BUT, the balances should NOT sum across months, quarters or years, because it makes no sense, and they'd be counted twice. Any longer time interval should show the close of period:

Quarter    WRONG    Correct
Q1         $1250    $850   // should be the sum of balances for the *last* month in Q1
  Type 1   $500     $300
  Type 2   $759     $550

If I use the stock "lastnonempty" aggregation, I seem to get only the one, literal last account row for a month, not the sum of the account balances for the last month. It's as if the total for the month is showing just the balance taken from one random account present in that month, and not the total. (I'm sure it's not really random, probably is picking one based on storage order or something)

I'm sure I am just doing something simple, wrong...

Answer 1

I think I solved this one: my source data having the balances was very sparse - there was a row only for non-zero balances, and NO rows for accounts when the balance is zero. That made everything screwy (imagine as inventory, where you have counts of parts, and those counts are often zero, but when they are 0 then the row is just missing from the fact table)

I was able to create a view at the data source that would "add back in" all the additional rows, with zero balances, and after that the ClosingPeriod() function started working as expected.