Forum Discussion
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...
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.
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
Super User
Super Userhello 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.