topic Re: First sale date per particular product per customer in DAX Commands and Tips

First sale date per particular product per customer

hcr4 — Tue, 29 Mar 2022 15:02:06 GMT

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.

Re: First sale date per particular product per customer

johnt75 — Tue, 29 Mar 2022 15:30:04 GMT

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

Re: First sale date per particular product per customer

hcr4 — Tue, 29 Mar 2022 21:00:20 GMT

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.

Re: First sale date per particular product per customer

johnt75 — Wed, 30 Mar 2022 08:02:32 GMT

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

Re: First sale date per particular product per customer

v-zhangti — Fri, 01 Apr 2022 06:39:36 GMT

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

Re: First sale date per particular product per customer

v-zhangti — Fri, 01 Apr 2022 06:55:08 GMT

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.