Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am trying to pick up repeat customers sales.🙂
I would like to change "Repeat customers from prior month sales"
to "Repeat customers from sales (sales in 6 months) "
For example if August, I would like to sum July to Feb.
sales data includes minus(returned) data😅 so would like to exclude that.
Any help would be appreciated!
Thanks,
Solved! Go to Solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Anonymous
Can you try this measure for the past 6 months sales from the previous month?
Repeat customers from sales (sales in 6 months) =
VAR currentCustomers = VALUES('SalesData'[customer])
VAR previousMonthDates = DATESINPERIOD('SalesData'[date].[Date],MAX(SalesData[date].[Date])-DAY(MAX(SalesData[date].[Date])),-6,MONTH)
VAR previousMonthCustomers =
CALCULATETABLE(
VALUES('SalesData'[customer]),
ALL('SalesData'),
previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN
SUMX(
repeatCustomersPreviousMonth,
VAR sales = CALCULATE(SUM('SalesData'[Sales])) return
IF( sales > 0 ,sales, BLANK())
)
________________________
Did I answer your question? Mark this post as a solution, this will help others!.
Click on the Thumbs-Up icon on the right if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi,
If my understanding is correct, you want to get a count of those customers who gave you business in the current month (shown in the row labels of the table visual) and in any of the 5 prior months. Am i correct?
Share the link from where i can download your PBI file.
thank you so much for your help.
Yes!
but not count 😅 amount of sales is what I would like to take.
I'm tired working with this porpblem😥
It will help you to understand what your current measure is doing, then update accordingly. You should really be using your Calendar table, see this post for why:
https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
Updating your existing measures to use the Calendar table and to be a tiny bit cleaner (without changing your logic too much, but I don't like using SUM within SUMX, even if you do enclose it in a CALCULATE, it's a bad habit to develop):
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thank you so much for your help🙂
I changed to use my calender table!
I have changed data also. my data was bad.Sorry.
1) Colored part did not show but the had a sales in previous 6 month.
2) do you know why the sum of red part is not same?
I downloaded to csv and checked the difference but data was same.
I'm sorry for my poor explanation.😅
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |