Calculate customers first purchase date by product

[av9]

New to Dax and I am trying to work out the first purchase date for each product, I have three tables: 

Customer

Customer A

Customer B

Customer C

Product

Product A

Product B

Product C

Calendar

June-20

May-20

Apr-20

Ideally, I'd like to see the data presented something like this:

Customer	Product	Date
Customer A	Product A	Apr-20
Customer A	Product B	Apr-20
Customer A	Product C	May-20

[HotChilli]

Is there an 'Orders' table?

If not, you'll have to provide more details about the tables you have and any relationships.

[av9]

Yes there is an orders table that contains the daily data , i.e. 

Customer	Date	Product
Customer A	30/04/2020	Product A

[HotChilli]

Ok, good, what have you tried so far?

[av9]

Tried using MIN to get the first date a purchase appears for a Customer :

First Purchase Date = CALCULATE ( MIN ( 'Order'[Date]), ALLEXCEPT ( Order, 'Order'[Date] ))

In a Table when I add the Product First Purchase date is repeated the same for every product. Not sure how to inlcude a product in dax formula.

[harshnathani · Accepted solution]

HI @av9 ,

Suppose this is your Order Table.

You can use the following measure

Measure =

MINX(

TOPN (1,'Table',MIN('Table'[Date]),ASC),'Table'[Date]

)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[HotChilli]

Can't we simplify the measure ? the visualisation will provide the context.  

If we want powerbi to do the work,  let's try dragging the date on to the visual and , in the Values well, click the dropdown on date and choose the appropriate aggregation (not Do Not Summarize)