How to iterate through two tables to create a new column?

[OB]

Hello,

I'm looking for some input or a full a solution for the problem below.  Also, I assume getting a solution using Power Query will be better, so that all transformations are done before the column loads into the report, but if there's a nice solution in DAX, please advise as well.

See the sample in the .xlsx Source File.  There are three tabs for three tables. 

Customers table has unique customer IDs

Transactions table lists all transactions for all customers. There can be many transactions for the each customer. A column has to be created in this table, which I called "TransTerms to be created".  It will be based on the rule applied to the third table: Contracts.

Contracts table contains all contracts - there can be many contracts for the same customer, depending on the time period.

What should be done is:

1) Take the Trans Date for Transaction1 from the Transaction table.

2) Go over all Contracts for that Customer in the Contracts table (there are three of them in the sample). 

3) Take the value from the Terms column for the record (contract) where the Transaction Date falls between Start and End dates in the Contracts table and write it to the new column "TransTerms to be created" in the Transactions table.

4) If the Transaction Date from the Transaction Table does not fall between any Start and End dates listed for that customer in the Constracts table, then write the value "30" in the "TransTerms to be created" column in the Transactions table against Transaction1.

5)  If the Customer has no contract listed in the Contracts table, write "30" in the "TransTerms to be created" column in the Transactions table against that transaction.

6) Repeat the same steps for Transaction2, i.e. take the Transaction Date for the Transaction2 from the Transaction table for the same customer and compare to all Contracts for that customer in the Contracts table.  Take the Terms value for the Contract where the Transaction Date is between Start and End dates and write it to the TransTerms to be created" column against Transaction2 in the Transactions table.  Otherwise, write "30".

7) Keep iterating like that through both tables to fill in "TransTerms to be created" column in the Transactions table.

Thanks a lot for your time!

[v-yulgu-msft]

Hi @OB,

I failded to download the attached source file, would you please share it via other tool, like OneDrive?

Regards,

Yuliana Gu

[OB]

Hi Yuliana,

I verified that the link works on my corp and on the external network for other people, so maybe you could try again? I shared it from my google drive via a shareable link.  I don't have OneDrive account with the option to share files.  The sample file is very small, so I've typed in data manually below and hopefully you can copy it in a new Excel file.

Customers:

123456

654321

987654

100000

Transactions:

Customer   TransNumber                 TransDate                   TransTerms Column To Be Created

123456             1                                   01/05/2005                               30

123456             2                                   10/03/2012                               15

123456             3                                   15/01/2018                               7

654321             101                               31/07/2009                               20

654321             201                               01/08/2010                               10 

654321             301                               31/05/2013                               20

654321             401                               01/12/2017                               25

987654             102                               01/02/2018                               30

100000             103                               01/03/2018                               30

Contracts:

Customer          Period               Start                         End                 Terms

123456                1                     01/01/2010            31/12/2011          20

123456                2                     01/01/2012            15/03/2012          15

123456                3                     16/03/2012            31/12/2099          7

654321                1                     01/07/2009            30/04/2010          20

654321                2                     01/05/2010            31/08/2010          10

654321                3                     01/09/2010            30/06/2013          20

654321                4                     01/07/2013            31/12/2099          25

987654                1                     01/03/2007            25/10/2007          20

Thanks for looking into this!