Forum Discussion
DAX Formula for repeat rate
Hello,
i want to find a user repeat rate on a specific period.
Basic data :
| date | User ID |
| 15/06/2021 | A |
| 15/06/2021 | A |
| 15/06/2021 | B |
| 16/06/2021 | C |
| 16/06/2021 | D |
| 18/06/2021 | E |
And i want to have :
| date | Users | reiterating users | reiterating rate | 2 days reiterating rate |
| 15/06/2021 | 3 | 1 | 33% | 33% |
| 16/06/2021 | 2 | 0 | 0% | 20% |
| 18/06/2021 | 1 | 0 | 0% | 0% |
What's the DAX formula for "2 days reitarting rate", please ?
Thanks
Hi viggo71
First add an Index column to the table to rank the dates.
Index = RANKX('Table','Table'[date],,ASC,Dense)Then create the following measure.
Measure 2 = VAR _currentDateIndex = MAX('Table'[Index]) VAR _startDateIndex = _currentDateIndex - 1 VAR _table = FILTER(ALL('Table'),'Table'[Index]>=_startDateIndex && 'Table'[Index]<=_currentDateIndex) VAR _table2 = SUMMARIZE(_table,'Table'[User ID],"Occurrences",COUNT('Table'[User ID])) VAR _allrecords = COUNTROWS(_table) VAR _repeatedUsers = COUNTROWS(FILTER(_table2,[Occurrences]>1)) RETURN DIVIDE(_repeatedUsers,_allrecords)+0Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
3 Replies
Here's a measure that shows one way to do it. Replace "Reiterate" with your actual table name.
Two Day Rate =
VAR thisdate =
MAX ( Reiterate[date] )
VAR prevdate =
CALCULATE ( MAX ( Reiterate[date] ), Reiterate[date] < thisdate )
VAR result =
CALCULATE (
DIVIDE (
COUNT ( Reiterate[ User ID] ) - DISTINCTCOUNT ( Reiterate[ User ID] ),
COUNT ( Reiterate[ User ID] )
),
FILTER (
ALL ( Reiterate[date] ),
Reiterate[date] <= thisdate
&& Reiterate[date] >= prevdate
)
)
RETURN
resultPat
Hi, thanks Pat but it doesn't work. I need a distinct count of duplicate ID on X rolling days.
Thanks
Hi viggo71
First add an Index column to the table to rank the dates.
Index = RANKX('Table','Table'[date],,ASC,Dense)Then create the following measure.
Measure 2 = VAR _currentDateIndex = MAX('Table'[Index]) VAR _startDateIndex = _currentDateIndex - 1 VAR _table = FILTER(ALL('Table'),'Table'[Index]>=_startDateIndex && 'Table'[Index]<=_currentDateIndex) VAR _table2 = SUMMARIZE(_table,'Table'[User ID],"Occurrences",COUNT('Table'[User ID])) VAR _allrecords = COUNTROWS(_table) VAR _repeatedUsers = COUNTROWS(FILTER(_table2,[Occurrences]>1)) RETURN DIVIDE(_repeatedUsers,_allrecords)+0Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.
