Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi
I need to report on new customers within a sales table. However a new customer is not over all time, but defined as whether they made a purchase in the last 6 months? If they have not purchased in 6 month prior then they are new.
The data is in a fairly standard fact table with dates,invoice numbers,customer, product detail, and sales amount ($)
Can anyone please assist?
Solved! Go to Solution.
Hi, @AndySmith
Thank you for your feedback.
Please kindly check the below picture and the pbix file link.
I hope I did not misunderstand your point.
The main DAX measure is below. And for others, you can check in the pbix file.
In the measure, I defined the new customers by using parameters.
For instance, in the PBIX file, you can select the number 6 to define the new customers as "not bought for last 6 months". Or you can change the slicer easily to the number 3 to change your definition of new customers.
The visualizations show customers count per month / new-customers count per month / quantity per month / new-customers-quantity per month. I hope you can easily apply this by using SUMX to multiply by the sales amount.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @AndySmith
Thank you for your feedback.
Related to your further questions,
- Inside the below DAX measure, the red-colored words are related to the parameter-selection. If you change the red-colored words to a number that you need, the measure will be fixed to only show the result that is related to that number only. And, also you need to change the topic as well, because inside the topic "N" months is also influenced by the parameter-selection.
Inside the sample pbix file (link below), I changed to 6.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @AndySmith
You may create a calculated table as below. The pbix file is attached in the end.
Calendar:
Calendar =
ADDCOLUMNS(
CALENDARAUTO(),
"YearMonth",
YEAR([Date])*100+MONTH([Date])
)
Then you could create a calculated column or a measure as below. There is no relationship between two tables.
Calculated column:
YearMonth = YEAR([Date])*100+MONTH([Date])
Measure:
Rolling 6 month count =
var t =
SUMMARIZE(
Sheet1,
Sheet1[Customer],
Sheet1[Product],
Sheet1[YearMonth],
"Flag",
var l =
TOPN(
6,
CALCULATETABLE(
DISTINCT('Calendar'[YearMonth]),
FILTER(
ALL('Calendar'),
[YearMonth]<=YEAR(TODAY())*100+MONTH(TODAY())
)
),
[YearMonth]
)
return
IF(
[YearMonth] in l,
1,0
)
)
return
COALESCE(
COUNTROWS(
FILTER(
t,
[Flag]=1
)
),0
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
Thank you so much. This is an incredible tool and you put it together so quickly. 2 questions if I may:
1) what would the measure code be for new customers if we took away the parameter choice? Instead simply hard coded 6 months
2 How do I create a data set/flag to show these new customers and what they have purchased?
Hi, @AndySmith
Thank you for your feedback.
Related to your further questions,
- Inside the below DAX measure, the red-colored words are related to the parameter-selection. If you change the red-colored words to a number that you need, the measure will be fixed to only show the result that is related to that number only. And, also you need to change the topic as well, because inside the topic "N" months is also influenced by the parameter-selection.
Inside the sample pbix file (link below), I changed to 6.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi, @AndySmith
Thank you very much for your question.
It will be very much appreciated if I could have a look at your sample data.
Without looking into your data, if I am allowed to suggest,
- you are trying to create a this-month-new-customers-report, right?
- in a measure, create three virtual tables by using VAR.
- one is a VALUES(Customers[customer-name]) for this month.
- the other is VALUES(Customers[customer-name]) for the last 6 months.
- then, the final is, EXCEPT (first table, second table).
- The final table is the list of new customers who did not buy during the last six months, but who did buy in this month.
- by using the final table, you can create order quantity, sales amount, and so forth.
I hope it helped.
if you can share your sample data, please let me have a look, and I will try to create measures to find out who are new customers.
Thank you.
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi - appreciate the response. Yes corect - I need to measure new sales by month. One point to note is that it is by product - so I a new customer purchases Product A and Product B this will count as 2 new listings. Hope this makes sense
Hi, @AndySmith
Thank you for your feedback.
Please kindly check the below picture and the pbix file link.
I hope I did not misunderstand your point.
The main DAX measure is below. And for others, you can check in the pbix file.
In the measure, I defined the new customers by using parameters.
For instance, in the PBIX file, you can select the number 6 to define the new customers as "not bought for last 6 months". Or you can change the slicer easily to the number 3 to change your definition of new customers.
The visualizations show customers count per month / new-customers count per month / quantity per month / new-customers-quantity per month. I hope you can easily apply this by using SUMX to multiply by the sales amount.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
95 | |
92 | |
86 | |
69 |
User | Count |
---|---|
162 | |
129 | |
125 | |
104 | |
98 |