Forum Discussion

morab's avatar
morab
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Repeat customers considering the base month

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 

 

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

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

    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's avatar
    Jihwan_Kim
    5 years ago

    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)
     
     

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

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

    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's avatar
      morab
      Icon for Advocate I rankAdvocate I
      5 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        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)