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!
Solved! Go to Solution.
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 🙂
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.
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:
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):
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.😅