Repeat customers considering the base month

[morab]

Hi there,

Good Day!

I have the customer transaction data set in the following link. I am trying to create a DAX measure to get the number of repeat customers who have purchased both in the base month & any selected month.

For example, Customer code - 100082181 have purchased in October'2019 (Which is the base month). But not purchase in November'2019. So he will not consider as a repeat customer of Nov'19. But have purchased in Dec'19 so he will consider as a repeat customer for Dec'19

Expected Result: In Nov'2019, 28 repeat customers & In Dec'2019, 35 repeat customers considering Oct'2019 as the base month.

Sample data: https://drive.google.com/file/d/1GBrpvtgWkNFPvzNUuZ0a4rB4SXq4prlU/view 

Thanks in advance 

[Jihwan_Kim · Accepted solution]

Hi, @morab 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

2 Repeat Customers count Oct base =

VAR currentcustomers =

VALUES ( Transactions[CustomerCode] )

VAR octcustomers =

CALCULATETABLE (

VALUES ( Transactions[CustomerCode] ),

FILTER ( ALL ( Dates ), Dates[Month] = 10 )

)

VAR intersectcustomers =

INTERSECT ( currentcustomers, octcustomers )

RETURN

IF( ISFILTERED(Dates), COUNTROWS ( intersectcustomers ))

https://www.dropbox.com/s/abra2u2snlsjtm8/morab.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Jihwan_Kim · Accepted solution]

Hi, @morab 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

2 Repeat Customers count Oct base =

VAR currentcustomers =

VALUES ( Transactions[CustomerCode] )

VAR octcustomers =

CALCULATETABLE (

VALUES ( Transactions[CustomerCode] ),

FILTER ( ALL ( Dates ), Dates[Month] = 10 )

)

VAR intersectcustomers =

INTERSECT ( currentcustomers, octcustomers )

RETURN

IF( ISFILTERED(Dates), COUNTROWS ( intersectcustomers ))

https://www.dropbox.com/s/abra2u2snlsjtm8/morab.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[morab]

Hi @Jihwan_Kim 

Thanks a lot for your prompt solution. 😊

Wow, It's working. 😀

However, Can It will be possible to apply a filter to count only those customers whose SalesKg is greater than zero within your shared DAX measure. Besides this, if I could also get the SalesKg of repeat customers.

For Example, Sales in Kg 77.54 for Nov'19 & Sales in Kg 115.64 for Dec'19

Thanks in advance

[Jihwan_Kim · Accepted solution]

Hi, @morab 

Thank you for your feedback.

I am not sure whether your numbers in the last question are correct or not.

Please try the below measure.

Repeat Customers SalesKG =

VAR currentcustomers =

VALUES ( Transactions[CustomerCode] )

VAR octcustomers =

CALCULATETABLE (

VALUES ( Transactions[CustomerCode] ),

FILTER ( ALL ( Dates ), Dates[Month] = 10 )

)

VAR intersectcustomers =

INTERSECT ( currentcustomers, octcustomers )

RETURN

CALCULATE( SUMX(Transactions, Transactions[SalesKg]), intersectcustomers)

[morab]

Hi @Jihwan_Kim 

[Jihwan_Kim]

You are welcome.  🙂