Sum Values Having Max Date

[#1]

With this data:

Date, Name, Value

1/1/12, Bob, 3

1/1/12, Bob, 4

3/1/10, Bob, 5

2/1/12, Jane, 1

2/1/12, Jane, 1

1/1/10, Jane, 5

I only need to return the sum of the values for each name, for only the max date for each name.  So in this scenario, the result would be:

1/1/12, Bob, 7

2/1/12, Jane, 2

Can someone please let me know what the max, sum, aggr formula (or any formula) for computing the correct sum's?

Thanks very much!

[#2]

very nice!

[vaibhavb]

Hi,

  I used same as you explained, I am facing same problem and solved up-to certain level but for Drill-Down it works for the First Level only, When i am drilling down to next level or on-wards won't work.

   sum(DAYSCLOSINGBALANCE*if(BALANCEDATE=aggr(nodistinct max(BALANCEDATE), FiscalYear),1,0))

  Here is FiscalYear is DrillDown

   FiscalYear -> Fiscal Month -> BalanceDate