cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Repeat customers sales  (6 months)

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.

 

My current measure is as follows:
Repeat customers from prior month =

VAR currentCustomers = VALUES('SalesData'[customer])
VAR previousMonthDates = PREVIOUSMONTH('SalesData'[date])
VAR previousMonthCustomers = CALCULATETABLE(VALUES('SalesData'[customer]),
ALL('SalesData'[date].[Month],'SalesData'[date].[MonthNo],'SalesData'[date].[Year])
, previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)

RETURN COUNTROWS(repeatCustomersPreviousMonth)

qa1.png

Any help would be appreciated!

Thanks,

1 ACCEPTED SOLUTION

To fix number 1) apologies, I used the wrong function, you need all dates within period, not just six months ago, so try this:

AK_Repeat customers from prior 6 month sales =
VAR currentCustomers = VALUES('SalesData'[customer])
VAR _currentDate = MAX('Calendar'[Date])
VAR previousMonthDates = DATESINPERIOD('Calendar'[Date],EOMONTH(_currentDate, -1), -6, MONTH)
VAR previousMonthCustomers = CALCULATETABLE(VALUES('SalesData'[customer]),
ALL('SalesData')
, previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN SUMX(FILTER(SalesData, SalesData[customer] IN repeatCustomersPreviousMonth), 'SalesData'[Sales])

For number 2) this is classic measure totals and I'm not really sure what you want to display. What it is currently doing, is looking at the date range that you have selected, and if the customer qualifies as a 'repeat customer' based on any date within the selected date range (2019 in your sample file), then it totals all their sales for the entire period of 2019, not just their sales for the months they qualified as repeat. Does that make sense? If you wanted something different, please explain.

You will also notice that the AK measures function when a single month slicer is placed on them, so are great for dynamic and current reporting in that sense.

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

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

View solution in original post

7 REPLIES 7
Fowmy
Super User
Super User

@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 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Ashish_Mathur
Super User
Super User

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.


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

@Ashish_Mathur 

 

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):

 

Repeat customers from prior month =

VAR currentCustomers = VALUES('SalesData'[customer])
VAR previousMonthDates = DATEADD('Calendar'[Date], -1, MONTH)
VAR previousMonthCustomers = CALCULATETABLE(VALUES('SalesData'[customer]),
ALL('SalesData')
, previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)

RETURN COUNTROWS(repeatCustomersPreviousMonth)
 
Repeat customers from prior month sales =
VAR currentCustomers = VALUES('SalesData'[customer])
VAR previousMonthDates = DATEADD('Calendar'[Date], -1, MONTH)
VAR previousMonthCustomers = CALCULATETABLE(VALUES('SalesData'[customer]),
ALL('SalesData')
, previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN SUMX(FILTER(SalesData, SalesData[customer] IN repeatCustomersPreviousMonth), 'SalesData'[Sales])
 
So to get that for 6 months, simply change -1 to -6: 
 
Repeat customers from prior 6 month sales =
VAR currentCustomers = VALUES('SalesData'[customer])
VAR previousMonthDates = DATEADD('Calendar'[Date], -6, MONTH)
VAR previousMonthCustomers = CALCULATETABLE(VALUES('SalesData'[customer]),
ALL('SalesData')
, previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN SUMX(FILTER(SalesData, SalesData[customer] IN repeatCustomersPreviousMonth), 'SalesData'[Sales])
 
Note you may also want to update your other measures to use the Calendar table: 
 
New customers sales =
VAR currentCustomers = VALUES('SalesData'[customer])
VAR currentDate = FIRSTDATE('Calendar'[Date])
VAR pastCustomers = CALCULATETABLE(VALUES('SalesData'[customer]),
ALL('SalesData')
, 'SalesData'[date]<currentDate
)
VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)

RETURN SUMX(FILTER(SalesData, SalesData[customer] IN newCustomers), 'SalesData'[Sales])

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

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
Not applicable

@AllisonKennedy 

 

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.

 

QA1.jpg

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.

 

QA2.jpg

 

I'm sorry for my poor explanation.😅

 

To fix number 1) apologies, I used the wrong function, you need all dates within period, not just six months ago, so try this:

AK_Repeat customers from prior 6 month sales =
VAR currentCustomers = VALUES('SalesData'[customer])
VAR _currentDate = MAX('Calendar'[Date])
VAR previousMonthDates = DATESINPERIOD('Calendar'[Date],EOMONTH(_currentDate, -1), -6, MONTH)
VAR previousMonthCustomers = CALCULATETABLE(VALUES('SalesData'[customer]),
ALL('SalesData')
, previousMonthDates
)
VAR repeatCustomersPreviousMonth = INTERSECT(currentCustomers, previousMonthCustomers)
RETURN SUMX(FILTER(SalesData, SalesData[customer] IN repeatCustomersPreviousMonth), 'SalesData'[Sales])

For number 2) this is classic measure totals and I'm not really sure what you want to display. What it is currently doing, is looking at the date range that you have selected, and if the customer qualifies as a 'repeat customer' based on any date within the selected date range (2019 in your sample file), then it totals all their sales for the entire period of 2019, not just their sales for the months they qualified as repeat. Does that make sense? If you wanted something different, please explain.

You will also notice that the AK measures function when a single month slicer is placed on them, so are great for dynamic and current reporting in that sense.

Please @mention me in your reply if you want a response.

Check out my Olympics report with live stats - KUDOS much appreciated

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
Not applicable

@AllisonKennedy 

Thank you for your help!!

It worked😉

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.