Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
tanah
New Member

Linking Finished and Ongoing Loan Contracts

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 AchieveWhat 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

1 REPLY 1
Selva-Salimi
Super User
Super User

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.