Formula returning a value for a particular date

Hi all,

 

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)).

 

Thank you

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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

    Like 1
  • Eric Fomba is this resolved?

    Like 2
  • Thanks It is really helpful!

    Like 1
Like Follow
  • Status Answered
  • 1 mth agoLast active
  • 3Replies
  • 28Views
  • 2 Following