Customers returned within 3 consecutive months after first month sale

DAX DAX Calculations
1

Hello,
Need help to calculate number of customers returned following 3 months after first month sale. Meaning that if a customer would have several purchases within a month, the month is what matters.
For example, C#11019
1st sale 8/16/2003 - This customer is returning because it had sales in 09,10,11
2nd - 9/5/2003 - This customer is returning - sales in 10, 11 & so on
3 - 9/14/2003
4 - 10/30/2003
5 - 11/9/2003
6 - 11/26/2003

I created calculated columns in Customers table:
BegFollowingMOafterFMS = ENDOFMONTH(Customers[1stSale Date]) +1 (returning the beginning of the month following the month of first sale)

Cons3moAfterFMS = DATE(YEAR(customers[1stSale Date]), MONTH(Customers[1stSale Date]) + 4, 1)-1

Measure to calculate returning customers:
RCTest3 =
CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]),
FILTER(ALL(Sales), Sales[OrderDate] >= RELATED(Customers[BegFollowingMOafterFMS]) &&
Sales[OrderDate] <=RELATED(Customers[Cons3moAfterFMS] ) ) )

Which gave me the total of 979 but no split by month because filters do not pass from Sales to Calendar table.

Then I tried to follow the pattern from one of the EDNA classes related to customers.
RC test =
VAR CustomerList = VALUES( Sales[CustomerKey] )
VAR ChurnDateone = [BegMoAfterFMS]
VAR ChurnDatetwo = [Cons3moAfterFMS]

RETURN
COUNTROWS(
FILTER(CustomerList,
CALCULATE(COUNTROWS(Sales),
FILTER(ALL(‘Calendar’),
‘Calendar’[Date] >= ChurnDateone &&
‘Calendar’[Date] < ChurnDatetwo ) ) >0 ) )

But it didn’t return the correct result. Strangely enough while I was prepping the pbix file for upload, it returned blanks.

Please find the attached pbix file
PP3_EF.pbix (1.8 MB)

2

Hi @fudzen88, thanks for providing the pbix file and for providing enough context to your problem. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

image

  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.

Thanks

3

Hello all, bumping this post for more visbility.

4

HI @fudzen88, while waiting for other members to jump in, I suggest using the forum search to discover if your query has been asked before by another member. Thanks!

5

Hi @fudzen88! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!