Get value for last date regarding selection for this date
Please help me solve my 2 issues:
I have data for ex:
Date amount account
2010-01-01 1 1
2010-01-10 2 1
2010-01-20 3 1
2010-01-21 4 1
2010-01-01 10 2
2010-01-15 2 2
I join it with Calendar so i have all dates (for some there is no data on accounts).
I created a pivot table for example :
Account, date, amount.
1 .I would like to recived only one value for each account based on date which i select , but if operation doesn't exists for selected date i would like to recived last value (for max(day) < current selection for this account). For example
I select 2010-01-15 :
account date amount
1 2010-01-01 2
2 2010-01-15 2
2. After each reload i would like to recived results based on current date, but user can change it for any past date.
But i can not create set for amount for this date, for example user select a 2010-01-19 i recived (with set) last operation date which is 2010-01-10 (for 1 account) but i can not recived amount for this date which is 2.