Addition of balances for accounts only during month they were opened

[#1]

Process Date	Account Number	Balance	Open Date
01/31/2014	XX1	$5	01/05/2014
01/31/2014	XX2	$10	01/12/2014
01/31/2014	XX3	$5	01/28/2014
02/28/2014	XX1	$10	01/05/2014
02/28/2014	XX2	$5	01/12/2014
02/28/2014	XX4	$10	02/12/2014
03/31/2014	XX1	$5	01/05/2014
03/31/2014	XX2	$10	01/12/2014
03/31/2014	XX5	$5	03/15/2014
04/30/2014	XX1	$10	01/05/2014
04/30/2014	XX6	$5	04/14/2014
04/30/2014	XX7	$10	04/30/2014
05/31/2014	XX1	$5	01/05/2014
05/31/2014	XX6	$10	04/14/2014
05/31/2014	XX7	$5	04/30/2014

I have the data shown above and I am trying to create a graph that will show me how many accounts were opened in a month and what was the closing balance for those accounts during the month they were opened. I am having problems with the addition of the balances, since it is duplicating the balance for the accounts during different months they are active and not just the balance for the month when the account was opened.

My dimension for my graph is open month and I would like it to show:

Month	# Accounts Opened	Balances for those Accounts During Opening month
January	3	$20
February	1	$10
March	1	$5
April	2	$15
May	0	$0

I have tried many formulas but haven't gotten the right one for the balance. Thank you for the help.

[MK_QSL · Accepted solution]

USE BELOW SCRIPT

==========================================

Test:

Load *, Month([Process Date]) as PDMonth, Month([Open Date]) as ODMonth;

Load

  Date(Date#([Process Date],'MM/DD/YYYY')) as [Process Date],

  [Account Number],

  Balance,

  Date(Date#([Open Date],'MM/DD/YYYY')) as [Open Date]

Inline

[

  Process Date, Account Number, Balance, Open Date

  01/31/2014, XX1, 5, 01/05/2014

  01/31/2014, XX2, 10, 01/12/2014

  01/31/2014, XX3, 5, 01/28/2014

  02/28/2014, XX1, 10, 01/05/2014

  02/28/2014, XX2, 5, 01/12/2014

  02/28/2014, XX4, 10, 02/12/2014

  03/31/2014, XX1, 5, 01/05/2014

  03/31/2014, XX2, 10, 01/12/2014

  03/31/2014, XX5, 5, 03/15/2014

  04/30/2014, XX1, 10, 01/05/2014

  04/30/2014, XX6, 5, 04/14/2014

  04/30/2014, XX7, 10, 04/30/2014

  05/31/2014, XX1, 5, 01/05/2014

  05/31/2014, XX6, 10, 04/14/2014

  05/31/2014, XX7, 5, 04/30/2014

];

==================================

Now create a Straight Table

Dimension

PDMonth

Expression

1) COUNT(Distinct IF(PDMonth = ODMonth, [Account Number]))

2) SUM(Distinct IF(PDMonth = ODMonth, Balance))

[#3]

Perfect! Thank you so much.. worked like a charm

[MK_QSL]

Kindly close the thread by selecting correct answer so that others can also use it..