Forum Discussion

nascarfan22's avatar
nascarfan22
Regular Visitor
1 year ago
Solved

DAX help calculating Repeat Customers

Hi, I’m working in Power BI and trying to create a DAX formula to classify purchasers as either Repeat Customers or Single Customers based on their Customer ID and the dates they made purchases. Fo...
"CALCULATE"
"DAX "
"DAX " "DAX help" 'Need help'
"FILTER"
distinctcount
  • Irwan's avatar
    Irwan
    1 year ago

    hello nascarfan22 

     

    i am using your sample data above, please try if this match to your need.

     

    1. create a calculated column to check single or repeat.

    Check =
    var _Count =
    COUNTX(
        FILTER(
            'Table',
            'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&
            'Table'[Sales Date]<>EARLIER('Table'[Sales Date])
        ),
        'Table'[Customer ID]
    )
    Return
    IF(
        _Count=1,
        "Repeat",
        "Single"
    )

    2. create two measures for single and repeat

    Single =
    CALCULATE(
        DISTINCTCOUNT('Table'[Customer ID]),
        'Table'[Check]="Single"
    )
    Repeat =
    CALCULATE(
        DISTINCTCOUNT('Table'[Customer ID]),
        'Table'[Check]="Repeat"
    )
     
    Hope this will help.
    Thank you.
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

nascarfan22 Create a calculated column to count the distinct purchase dates for each customer:

DAX
DistinctPurchaseDates =
CALCULATE(
DISTINCTCOUNT('Sales'[Sales Date]),
ALLEXCEPT('Sales', 'Sales'[Customer ID])
)

 

Create a calculated column to classify each customer as either "Single Customer" or "Repeat Customer":

DAX
CustomerType =
IF(
'Sales'[DistinctPurchaseDates] = 1,
"Single Customer",
"Repeat Customer"
)

 

To get the count of Single Customers and Repeat Customers, you can create measures:

DAX
Single_Customers =
CALCULATE(
DISTINCTCOUNT('Sales'[Customer ID]),
'Sales'[CustomerType] = "Single Customer"
)

Repeat_Customers =
CALCULATE(
DISTINCTCOUNT('Sales'[Customer ID]),
'Sales'[CustomerType] = "Repeat Customer"
)

  • nascarfan22's avatar
    nascarfan22
    Regular Visitor
    1 year ago

    Hi, this solution gives me a split of customers that is 90% repeat and 10% single. When I calculate it in excel it's closer to 60% repeat and 40% single. Is there a step that could have been missed?

    • Irwan's avatar
      Irwan
      Super User
      1 year ago

      hello nascarfan22 

       

      i am using your sample data above, please try if this match to your need.

       

      1. create a calculated column to check single or repeat.

      Check =
      var _Count =
      COUNTX(
          FILTER(
              'Table',
              'Table'[Customer ID]=EARLIER('Table'[Customer ID])&&
              'Table'[Sales Date]<>EARLIER('Table'[Sales Date])
          ),
          'Table'[Customer ID]
      )
      Return
      IF(
          _Count=1,
          "Repeat",
          "Single"
      )

      2. create two measures for single and repeat

      Single =
      CALCULATE(
          DISTINCTCOUNT('Table'[Customer ID]),
          'Table'[Check]="Single"
      )
      Repeat =
      CALCULATE(
          DISTINCTCOUNT('Table'[Customer ID]),
          'Table'[Check]="Repeat"
      )
       
      Hope this will help.
      Thank you.