Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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!
Solved! Go to Solution.
Hi,
You may download my PBI file from here.
Hope this helps.
Sample Sales Table
Customer | Product | Period | Sales Amt |
Joe | A | Jan-22 | $5 |
Joe | A | Feb-22 | $6 |
Joe | A | Apr-22 | $6 |
Joe | B | Feb-22 | $20 |
Joe | B | May-22 | $15 |
Joe | C | Jan-22 | $7 |
Joe | C | Sep-22 | $15 |
Sally | A | Feb-22 | $7 |
Sally | A | Apr-22 | $7 |
Sally | C | Feb-22 | $15 |
Sally | C | May-22 | $17 |
Bob | A | Apr-22 | $18 |
Bob | A | Jun-22 | $15 |
Bob | B | May-22 | $15 |
Bob | B | Jun-22 | $17 |
Output using
First Sales Month=min('Sales'[Period])
I only care about Product A and Product B
A | B | |
Joe | Jan-22 | Feb-22 |
Sally | Feb-22 | |
Bob | Apr-22 | May-22 |
When I filter using the Period from the Date Table
If i filter for Jan I want to see
A | B | |
Joe | Jan-22 |
If i filter for Feb 2022 I want to see
A | B | |
Joe | Feb-22 | |
Sally | Feb-22 |
What i actually see is because the filter is basically eliminating Jan and seeing Joe sold product A in Feb also:
A | B | |
Joe | Feb-22 | Feb-22 |
Sally | Feb-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.
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.
CustomerKey | DateFirstPurchase |
21602 | 11/1/2004 |
22517 | 21/4/2004 |
22518 | 2/2/2004 |
22714 | 20/1/2004 |
22871 | 12/5/2004 |
23830 | 13/6/2004 |
23838 | 14/3/2004 |
24839 | 3/12/2003 |
24840 | 25/8/2003 |
24848 | 12/11/2002 |
26453 | 12/10/2003 |
ProductKey | OrderDate | CustomerKey |
376 | 2/7/2003 | 16688 |
376 | 7/7/2003 | 18212 |
376 | 9/7/2003 | 16702 |
376 | 10/7/2003 | 18246 |
376 | 11/7/2003 | 18243 |
376 | 15/7/2003 | 18214 |
376 | 23/7/2003 | 18222 |
376 | 24/7/2003 | 18210 |
376 | 3/8/2003 | 18250 |
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.
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
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
208 | |
94 | |
64 | |
60 | |
57 |