Show only initial customer purchase

[orana]

Hello,

I currently have a bar chart that shows the number of customers who made purchases over a year. The y-axis shows the months and the x-axis shows a count of customer. A customer could have made >1 purchases per month and/or >1 purchase per year. 

I would like to have a bar chart to only show a customer's initial purchase.  For example if customer A purchased twice in October - once on October 2nd & another time on Oct 15th and 3 times in November (3, 15, 25). The bar chart will only show customer A purchase on October 2nd. 

[Ashish_Mathur]

Hi,

Create a Calendar Table with a relationship from the Date column of the Purchase date table to the Date column of the Calendar Table.  In the Calendar Table, write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name column by the month number column.  Via slicers, select Year and Month name.  To your matrix visual, drag Customer Name and drag this measure

Measure 1 = min(Data[Purchase date])

Does this help?

[v-jayw-msft]

Hi @orana ,

You could create a measure as below.

Measure = 
var first_date = CALCULATE(MIN('Table'[date]),ALLEXCEPT('Table','Table'[customer]))
return
IF(SELECTEDVALUE('Table'[date])=first_date,SELECTEDVALUE('Table'[purchase]),BLANK())

Best Regards,

Jay

[orana]

Looking at my data, the dates seem to all be the first of each month so for the example provided - "a" would have the date 9/1/21, 9/1/21, 10/1/21. Is there a way to get around this?

[orana]

Hey,

It doesn't seem to be working. To help, I created a power bi example and shared the accompany excel file in the hopes that this will help. If you look at Sheet 2, you'll see that in Sept and November the customer "my" appears in both months. Ideally, i would only want "my" to show up when "my" made the its first purchase. This would be in Sept. 

Power BI example Accompanying Excel file 

Best,

Oscar

[v-jayw-msft]

Hi @orana ,

Using the above formula will get two records of 9/1/2021 for "a". What is the expected result?

Best Regards,

Jay

[orana]

This changes entire data set around and impacts my abiilty to use this information in other charts. Is there another way?

[ryan_mayu]

@orana 

you can also try to create a new table

Table 2 = SUMMARIZE('Table','Table'[customer],"date",min('Table'[date]))

[ryan_mayu]

@orana 

maybe you can try group by the table to get the min date