Calculating AR

[jagan]

Hi All,

I stuck with an expression in calculating the AR, please check the data below

Invoice#	Transaction Date	Transaction#	Balance
100	1/1/2012	1000	1000
100	1/10/2012	1005	900
100	2/10/2012	1010	800
101	2/10/2012	1020	5000
101	2/15/2012	2012	4000
101	2/10/2012	2015	3000
101	2/10/2012	2020	1000

I am providing a Date object to select As of Date.  Based on the selected date, i need to sum up the balance field by getting the amount Maximum Transaction# of an invoice. 

Suppose if I selected date as 2/10/2012, for Invoice# 100 the AR is 800.

Suppose if I selected date as1/20/2012, for Invoice# 100 the AR is 900.

  I have around 10 million invoices, so if I use Aggr() it is taking to much time to load the chart.  Is there any other method to do this.  I also tried FirstSortedValue(), but it is given me correct values when I used Invoice# as dimension, but my chart has no Invoice# dimension.

Regards,

Jagan.