Finding the Date a customer first purchased from a specific Contract.

[belmore]

I would like to identify the Date a Finding the Date a customer first purchased from a specific Contract. 

I believe I would need to create a new table by joining the customer table and the contracts table.  (there may be a better method)

I would like advice on how to create the new table as well as how to create the Contract FOD(first order date) field.

My three tables:

Customer Table

Contract Table

Sales table

I would like to create a new table by joining the customer table and the contracts table. Also, Sales table includes the following fields --- CustomerID, ContractID, GroupID
 

Customer Table

CutomerID                       Customer GroupID

-Customer A                     -Group A

-Customer B                     -Group B

-Customer C                     -Group A

Contract Table

ContractID                         Contract GroupID

-ContractA                         -Group A

-ContractB                         -Group A

-ContractC                         -Group A

-ContractD                         -Group B

-ContractE                         -Group B

I would like to create a new table by JOIN (ing) the Contracts table and the Customer table where the Contract Group ID = Customer Group ID....

NEW Table

 CustomerID                          ContractID                    GroupID                Contract FOD(First Order Date) 

-Customer A                           -ContractA                    -Group A              05/05/2017 (date Cust A first order from Contract A)

-Customer A                           -ContractB                    -Group A              08/05/2016 (date Cust A first order from Contract B)

-Customer A                           -ContractC                    -Group A

-Customer C                           -ContractA                    -Group A

-Customer C                           -ContractB                    -Group A

-Customer C                           -ContractC                    -Group A

-Customer B                           -ContractD                    -Group B

-Customer B                           -ContractE                     -Group B

I would like advice on how to create the new table as well as how to create the Contract FOD(first order date) field.

Thanks

[Ashish_Mathur]

Hi,

Creating the three column table should not be difficult.  What i cannot understand is the FoD?  In which of the 3 tables do you have the Date of purchase.  Share the link from where we can download your data in an Excel/PBIX file with all 3 Tables.

[v-shex-msft · Accepted solution]

Hi @belmore,

Which table you stored the date column? Please clarify this.

For your scenario, you can refer to below steps to create the merge table.

1. Enter into query editor.
2. Create a new query to merge customer and contract.

let
    Source = Table.Join(Contract,"Contract GroupID",Customer,"Customer GroupID"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"ContractID", "Contract GroupID", "CutomerID"})
in
    #"Removed Other Columns"

3. Save and apply the change.

4. Add calculator to lookup date from contract/customer table.

If Date stored in Contract table:
FOD = MINX(FILTER(ALL(Contract),[ContractID]=EARLIER(Combine[ContractID])&&[Contract GroupID]=EARLIER(Combine[Contract GroupID])),[Date])

If Date stored in Customer table:
FOD = MINX(FILTER(ALL(Customer),[CutomerID]=EARLIER(Customer[CutomerID])&&[Contract GroupID]=EARLIER(Customer[Customer GroupID])),[Date])

Result:

Regards,

Xiaoxin Sheng