Linking Finished and Ongoing Loan Contracts

[tanah]

I'm trying to track each contract history by linking the finished and ongoing loan by finding:

1. first_contract_by_id_num,
2. first_contract_by_collateral,
3. previous_contract_by_id_num,
4. previous_contract_by_collateral,
5. next_contract_by_id_num,
6. next_contract_by_collateral

Below is an example of what i want to achieve:

What I want to Achieve

Sample Data Note:

• the records are updated daily which is shown by date_period, but only for Aug 2024 and Sep 2024, so there'll be repetition for those months,
• if the contract is completed by that month, it's not gonna show up in the next month of date_periode
• finished/completed contract can be seen from the contract_finish_date, where it's not blank
• an id_num can have two or more active contract (contract_finish_date is blank) but each will have different collateral and not the other way around

Sample Data File: https://drive.google.com/file/d/1QbltPFswlQHFW6L2pCpJxoCIZSgMvEar/view?usp=sharing

[Selva-Salimi]

Hi @tanah 

you can create columns as follows:

1. first_contract_by_id_num =
   var min_date= calculate(min(contract_sample_data[contract_start_date]) , FILTER(contract_sample_data, contract_sample_data[id_num]=earlier(contract_sample_data[id_num])))

   return

   calculate( min(contract_sample_data[contract_num]) , filter(contract_sample_data , contract_sample_data[id_num]=EARLIER(contract_sample_data[id_num]) && contract_sample_data[contract_start_date]= min_date))
2. first_contract_by_collateral= 
   var min_date= calculate(min(contract_sample_data[contract_start_date]) , FILTER(contract_sample_data, contract_sample_data[id_num]=earlier(contract_sample_data[id_num])))
   return
   calculate( min(contract_sample_data[contract_num]) , filter(contract_sample_data , contract_sample_data[collateral]=EARLIER(contract_sample_data[collateral]) && contract_sample_data[contract_start_date]= min_date))
3. previous_contract_by_id_num = 
   calculate( min(contract_sample_data[contract_num]) , FILTER(contract_sample_data , contract_sample_data[id_num]=EARLIER(contract_sample_data[id_num]) && contract_sample_data[contract_start_date]< EARLIER(contract_sample_data[contract_start_date])))
4. previous_contract_by_collateral = 
   calculate( min(contract_sample_data[contract_num]) , FILTER(contract_sample_data , contract_sample_data[collateral]=EARLIER(contract_sample_data[collateral]) && contract_sample_data[contract_start_date]< EARLIER(contract_sample_data[contract_start_date])))
5. next_contract_by_id_num = 
   calculate( min(contract_sample_data[contract_num]) , FILTER(contract_sample_data , contract_sample_data[id_num]=EARLIER(contract_sample_data[id_num]) && contract_sample_data[contract_start_date] > EARLIER(contract_sample_data[contract_start_date])))

   6. next_contract_by_collateral = 

   calculate( min(contract_sample_data[contract_num]) , FILTER(contract_sample_data , contract_sample_data[collateral]=EARLIER(contract_sample_data[collateral]) && contract_sample_data[contract_start_date] > EARLIER(contract_sample_data[contract_start_date])))

   If this post helps, then I would appreciate a thumbs up 👍  and mark it as the solution to help the other members find it more quickly.