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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
belmore
Helper II
Helper II

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.

 
 
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
1 REPLY 1
Eric_Zhang
Employee
Employee

@belmore

What relationship among those tables? Where is the order date from? For further suggestion, could you post more details, sample data of the sales tables? Even better you can upload the pbix file to Onedrive/GoogleDrive and share the link.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.