DAX Help-Date a Customer placed their First Order from a specific Contract.

[belmore]

I would like to find the Date a Customer placed their First Order from a specific Contract. I believe (not 100% sure) the best method is to create a new table by joining the customer table and the contracts table.

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

3 tables
Customer Table
Contract Table
Sales table

I would like to create a new table by joining the customer table and the contracts table.

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.

The Sales table includes the following fields --- CustomerID, ContractID, GroupID

Thanks for any advice

[Phil_Seamark · Accepted solution]

Hi @belmore

This calculated table might be getting close to what you need.

New Table = 
SUMMARIZE(
    FILTER(
        CROSSJOIN('Contract','Customer'),
        'Contract'[Contract GroupID] = 'Customer'[Customer GroupID]
        ),
        'Customer'[Customer ID] ,
        'Contract'[ContractID] ,
        "Contract FOD" , MIN('Contract'[Order Date])   
    )

[belmore]

Phil,

Thank you! for responding....your suggestion has me real close....the Contract 'FOD' is the only missing piece...the [Order Date] info is stored within my 'Sales Table' ---- and both the 'Customer' and  'Contract' tables have a one-to-many relationship with the 'Sales' table. Any advice on how to pull this into the New table?

I not sure how to find the MIN [Order Date] at the sales.customerID & sales.contractID level.

Thank you again for your help