Hello - I have this requirement that seems like it should be fairly
straight forward to solve - but I can't seem to get it to work quite
right. It's a basic accounting system, with charges/receipts occuring
daily. What I also need to get - is what the current balance of an
account was at any given point in time in history. For example:
John Doe gets an invoice for:
$100 on 01/01/2008
He makes payments of:
$10 on 01/10/08
$10 on 01/15/08
$10 on 02/10/08
Looking at each level of the time hierarchy should look like this:
Date: Q1 2008 Amount Billed: $100 Amount Paid: $30 Amount Outstanding:
$70
Date: Jan 2008 Amount Billed: $100 Amount Paid: $20 Amount
Outstanding: $80
Date: Feb 2008 Amount Billed: $0.00 Amount Paid: $10 Amount
Outstanding: $70
Date: Feb 10, 2008 Amount Billed: $0.00 Amount Paid: $10 Amount
Outstanding: $70
Date: Feb 20, 2008 Amount Billed: $0.00 Amount Paid: $0.00 Amount
Outstanding: $70
I tried to use the Adventure Works example with the DimAccount
dimension, that had an Account Balance parent, and two children of
Charge and Receipt. Charge had a unary operator of '+' and Receipt had
a unary operator of '-'. This sort of worked - except not down to the
day level. For example, on Feb 10, 2008 it would take $0.00 - $10 and
give a balance of -$10.
I then created a calculated measure as:
Aggregate
(
NULL:[Transaction Date].[Year - Quarter - Month -
Date].CurrentMember,
[Measures].[Amount]
)
This seemed to work (keep in mind the Amount measure automatically
subtracts Receipts from Charges because of the unary operator) but I
fear a performance hit on this when the DW gets populated with all the
invoices (roughly 24,000 per year).
The ultimate goal is to be able to get Amount Paid, Amount Billed,
Amount Outstanding and the Avg. Days Outstanding for any point in
time.
Any performance suggestions?
Any links to related articles?
Any experience with accounting related type cubes?
Anything would help - thanks very much!!
Tracie
>> Stay informed about: Historical Balance ??