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.

mahoneypat's avatar
mahoneypat
Microsoft Employee
5 years ago

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
    result

 

 

Pat