DAX Formula for repeat rate

[viggo71]

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

[v-jingzhang · Accepted solution]

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.

[viggo71]

Hi, thanks Pat but it doesn't work. I need a distinct count of duplicate ID on X rolling days.

Thanks

[mahoneypat]

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