Forum Discussion

belmore's avatar
belmore
Icon for Helper II rankHelper II
8 years ago
Solved

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

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

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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])   
    )

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      belmore
      Icon for Helper II rankHelper II
      8 years ago

      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