First sale date per particular product per customer

[hcr4]

I have two tables - Customer and Product. Customer table has a FirstSaleDate column showing the first time a sale was made to a particular customer regardless of the product and Product has FirstProductSale showing the first time a particular product was sold regardless of the customer. I want to create a measure that will provide me with a field (ideally in the Product table) showing when a customer first bought a particular product.

My DAX skills are lacklustre and I'm not sure where to begin. Appreciate any help, thanks.

[v-zhangti · Accepted solution]

Hi, @hcr4 

I briefly simulated some data that I hope will fit your situation.

Column:

first bought = 
LOOKUPVALUE (
    Customer[FirstSaleDate],
    'Product'[FirstProductSale], [FirstProductSale]
)

Measure:

first bought M = 
LOOKUPVALUE (
    Customer[FirstSaleDate],
    'Product'[FirstProductSale], SELECTEDVALUE('Product'[FirstProductSale])
)

LOOKUPVALUE: https://docs.microsoft.com/dax/lookupvalue-function-dax 

Please see the attachment for details.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-zhangti]

Hi, @hcr4 

Can you provide some of the example data? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. 

Best Regards

[johnt75]

Try MINX( 'Order Lines', RELATED(Orders[Order date])

[johnt75]

If you also have a Sales table linked to both Product and Customer then MIN(Sales[Order date]) will give the first time a product was ordered. You can put that measure in a table with a Customer and a Product and it will show the first order for that product by that customer

[hcr4]

Hi, thanks for the reply. The Customer and Product tables are 'linked' via Orders and Order Lines tables. I.e. Product <--> Order Lines <--> Orders <--> Customers

So in this case I don't think this works.