Forum Discussion

viggo71's avatar
viggo71
Frequent Visitor
5 years ago
Solved

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/20...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

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

     

    Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as the solution to help other members find it.

v-jingzhang's avatar
v-jingzhang
Community Support
5 years ago

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

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.