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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
JimSales81
Frequent Visitor

List of Customers who had first Purchase Date in a Specified Month

Hello,


I have a Sales FACT (has Date Key, Product Key, customer Key, and sales amount), Date DIM, Customer DIM, and Product DIM.

I'm trying to determine a list of customers who  purchased a specific product (using a slicer from Product Table) in a specific Month(using a Slicer from Date Table) for the first time.  I.e. If Joe Smith bought Product A in Jan 2022 for the first itme, I want Joe to show only when i hit the slicer for Jan 2022, not Feb 2022 (or any other month!) when he bought Product A again.


When is do 

First Month of Purchase=Min('Sales'[Year Month])

It works in a Matrix Visual perfectly until i start filtering. Then it basically shows me anyone who purchased that product that month instead of just the customers who purchased the product for the First Time.

 

I assume i need to make a VAR table to achieve this but i can't get the filtering working when i try.

 

Thank You!

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

10 REPLIES 10
JimSales81
Frequent Visitor

 

Sample Sales Table

CustomerProductPeriodSales Amt
JoeAJan-22$5
JoeAFeb-22$6
JoeAApr-22$6
JoeBFeb-22$20
JoeBMay-22$15
JoeCJan-22$7
JoeCSep-22$15
SallyAFeb-22$7
SallyAApr-22$7
SallyCFeb-22$15
SallyCMay-22$17
BobAApr-22$18
BobAJun-22$15
BobBMay-22$15
BobBJun-22$17

 

Output using 

First Sales Month=min('Sales'[Period])

I only care about Product A and Product B

 

 AB
JoeJan-22Feb-22
SallyFeb-22 
BobApr-22May-22

 

When I filter using the Period from the Date Table


If i filter for Jan I want to see

 AB
JoeJan-22 

 

If i filter for Feb 2022 I want to see

 AB
Joe Feb-22
SallyFeb-22 

 

What i actually see is because the filter is basically eliminating Jan and seeing Joe sold product A in Feb also:

 AB
JoeFeb-22Feb-22
SallyFeb-22 

 

How can we keep the first sales date basically static while filtering for date?  I assume i need a Var table or I need to do soemthing with the filters using All(), Allexcept(), or allselected()

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

The PBIX file is no longer available in the given link. Can you please provide a new link for that?

Hi,

I do not have the file.  Share some data to work with, explain the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

CustomerKeyDateFirstPurchase
2160211/1/2004
2251721/4/2004
225182/2/2004
2271420/1/2004
2287112/5/2004
2383013/6/2004
2383814/3/2004
248393/12/2003
2484025/8/2003
2484812/11/2002
2645312/10/2003

 

ProductKeyOrderDateCustomerKey
3762/7/200316688
3767/7/200318212
3769/7/200316702
37610/7/200318246
37611/7/200318243
37615/7/200318214
37623/7/200318222
37624/7/200318210
3763/8/200318250

    

From the above, I need to calculate Number of customer Who Returned Within Following 3 Months. Following 3 months means- i) If any customer's first purchase was in July, I want to know whether he bought again in Aug, Sept, or Oct. ii) If that customer bought twice in July, that doesn’t count. I only look for whether he came back in Aug-Oct. So July 1 first purchasers and July 31 first purchasers are exactly the same for this one.

Ashish,


This works properly but the performance is incredibly slow.   Any thoughts on how to improve performance?

Thanks,
Jim

Hi, 

Would you provide me that PBIX file as it is no more available in the link.

Hi,

No, i would not know of a faster way.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

see attached

lbendlin
Super User
Super User

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.