Formula returning a value for a particular date
I have a worksheet retracing the amount my clients have in the account
from the moment they joined the bank to now.
I would like to write a formula returning the amount of money they had when they joined (date_A) and another formula for their current assets. I managed to create the second one. But I don't know how to create the first (the amount of money they had at the moment they join (date_A)).
Assuming we have the following data
Balance_Date Customer Balance 1/10/19 Damian 100 1/11/19 Damian 80 1/12/19 Damian 120 1/9/19 Marie 500 1/10/19 Marie 400 1/11/19 Marie 1000 1/12/19 Marie 700
I created two functions to get the min date and max date for each customer.
Min Date = group_aggregate ( min ( Balance_Date ) , query_groups ( ) , query_filters ( ) ) Max Date = group_aggregate ( max ( Balance_Date ) , query_groups ( ) , query_filters ( ) )
I then create two functions to get the first and last balance of the period.
Most recent Balance = sum_if ( date = max date , balance ) First Balance = sum_if ( date = min date , balance )
the following is a screen shot of the search