DAX help calculating Repeat Customers

[nascarfan22]

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.

For example:

• If a customer makes multiple purchases on the same date (e.g., 5/1/2025), they are considered a Single Customer.

• If they make purchases on multiple different dates (e.g., 5/1/2025 and 5/2/2025), they are classified as a Repeat Customer.

If I were doing this in Excel, I would use a Pivot Table to distinct count the number of purchase dates per Customer ID, then use a COUNTIF to determine how many customers have only one purchase date (Single Customer) versus more than one (Repeat Customer). This is what I’m trying to replicate using DAX in Power BI.

These are the DAX formulas I've tried:

Single_Customers = COUNTROWS(filter(DISTINCT('Sales'[Customer ID]),CALCULATE(COUNT('Sales'[Sales Date]))=1))
Repeat_Customers = COUNTROWS(filter(DISTINCT('Sales'[Customer ID]),CALCULATE(COUNT('Sales'[Sales Date]))>1))

Single_Customers = 
COUNTROWS(
    FILTER(
        VALUES('Sales'[Customer ID]),
        CALCULATE(DISTINCTCOUNT('Sales'[Sales Date])) = 1
    )
)

Repeat_Customers = 
COUNTROWS(
    FILTER(
        VALUES('Sales'[Customer ID]),
        CALCULATE(DISTINCTCOUNT('Sales'[Sales Date])) > 1
    )
)

Here is some sample data. If I did this in excel with a pivot table and distinct count it, I'd have 5 Single Customers and 1 Repeat Customer. 

Customer ID	Sales Date
3808	2/11/2025
1833	5/16/2025
3808	2/11/2025
2879	2/11/2025
1727	5/16/2025
1727	4/18/2025
2036	2/11/2025
1128	4/18/2025

[V-yubandi-msft]

Hi @nascarfan22 ,

Could you confirm if your issue has been resolved or if you're still facing challenges. If the solution worked for you, please consider marking it as the accepted solution so it can help other community members. If you're still experiencing issues, feel free to share the details.

Thank You.

[V-yubandi-msft]

Hi @nascarfan22 ,
If you’ve gone through the shared responses and reference links, and they helped solve your issue, please mark the most helpful reply as the Accepted Solution. It really helps others in the community who might be facing the same kind of problem.

Thank You.

[V-yubandi-msft]

Hi @nascarfan22 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[V-yubandi-msft]

Hi @nascarfan22 ,

Thank you for posting your query in the Microsoft Fabric Community.

@Irwan , has provided a detailed response that aligns with your requirements. Could you please review his guidance along with the provided PBIX file.

If you need any further clarification or additional support, feel free to let us know we’re ready  to assist.

Best regards,
Yugandhar.

[bhanu_gautam]

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

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 · Accepted solution]

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.