I have solved a similar request by cross blending the data source with itself and adding 2 fields for comparision:

1. textDate (table 1)  = TODATE(date,"%Y-%m-%d","%Y-%m-%d")
   

2. maxDate (table 2) = TODATE(MAX(date),"%Y-%m-%d","%Y-%m-%d")
   
   

   then to get the balance for the latest date:

3. SUM(IF(textDate = maxDate, balance,0))
   

enter image description here
enter image description here
enter image description here
enter image description here