Users this month that existed in prior month

[chrisw888]

Hi,

I am new to BI. I was wondering would anyone have any familiarity with creating a bar chart of unique but with a line showing the same users that existed in the prior month?

As a guide, I have 100 unique users in month 1, 140 in month 2, and 180 in month 3. Of the 140 users in month 2, 50 of them also existed in month 1. I am trying to plot a line on top of the bar chart that shows then in Jan, 35.7 percent of users were repeat users that existed in the prior month. In month 3, of the 180, 60 of them had been present in month 2, so the line would then show 33.33 percent.

All in what I am trying to show is each month these are the amount of unique users I have in the month, but of these users, what percent of them are repeat users that existed in the previous month or months.

Thanks

Chris

[tamerj1 · Accepted solution]

Hi @chrisw888 

Assuming that you are using a date table you can try

RepeatRate =
VAR CurrentDate =
    MAX ( 'Table'[Date] )
VAR CurrentUsers =
    VALUES ( 'Table'[UserID] )
VAR PreviousMonthUsers =
    CALCULATETABLE (
        VALUES ( 'Table'[UserID] ),
        FILTER (
            ALL ( 'Date'[Date] ),
            FORMAT ( 'Date'[Date], "YYYYMM" )
                = FORMAT ( EDATE ( MAX ( 'Date'[Date] ), -1 ), "YYYYMM" )
        ),
        ALL ( 'Date' )
    )
VAR ReturningUsers =
    INTERSECT ( PreviousMonthUsers, CurrentUsers )
RETURN
    DIVIDE ( COUNTROWS ( ReturningUsers ), COUNTROWS ( CurrentUsers ) )

[chrisw888]

Thank you so much both, I have got sorted with the solution. The first was giving me a straight line for some reason at with a value of 1, second has charted it exactly how I was looking it.

Thank you so much for your help, I will spend some time to look at writing DAX formula from scratch. 

[tamerj1 · Accepted solution]

Hi @chrisw888 

Assuming that you are using a date table you can try

RepeatRate =
VAR CurrentDate =
    MAX ( 'Table'[Date] )
VAR CurrentUsers =
    VALUES ( 'Table'[UserID] )
VAR PreviousMonthUsers =
    CALCULATETABLE (
        VALUES ( 'Table'[UserID] ),
        FILTER (
            ALL ( 'Date'[Date] ),
            FORMAT ( 'Date'[Date], "YYYYMM" )
                = FORMAT ( EDATE ( MAX ( 'Date'[Date] ), -1 ), "YYYYMM" )
        ),
        ALL ( 'Date' )
    )
VAR ReturningUsers =
    INTERSECT ( PreviousMonthUsers, CurrentUsers )
RETURN
    DIVIDE ( COUNTROWS ( ReturningUsers ), COUNTROWS ( CurrentUsers ) )

[chrisw888]

Hi Tamerj,

Sorry to be a pain, would you know if I wanted to edit the count to exclude blanks, how I could factor this in? I can figure out how to exclude the blanks by doing DISTINCTCOUNTNOBLANK for the bar chart element (real data is 182 unique users and one blank line), but the above then pulls 183 unique users as I have one user in the data the results with a blank as they have no user ID.

In the attached image for example, the bar for Feb is 182 unique users (excluding the blank line). Of this, 58 were users in January, so the line should read at 31.87%, but it currently reads at 31.69%, as it is seeing 58/183.

[tamerj1 · Accepted solution]

@chrisw888 
I believe it should be enough just filter out the blanks from from current month users, as INTERSECT will take care of the previous month. Please try

RepeatRate =
VAR CurrentDate =
    MAX ( 'Table'[Date] )
VAR CurrentUsers =
    FILTER ( VALUES ( 'Table'[UserID] ), 'Table'[UserID] <> BLANK () )
VAR PreviousMonthUsers =
    CALCULATETABLE (
        VALUES ( 'Table'[UserID] ),
        FILTER (
            ALL ( 'Date'[Date] ),
            FORMAT ( 'Date'[Date], "YYYYMM" )
                = FORMAT ( EDATE ( MAX ( 'Date'[Date] ), -1 ), "YYYYMM" )
        ),
        ALL ( 'Date' )
    )
VAR ReturningUsers =
    INTERSECT ( PreviousMonthUsers, CurrentUsers )
RETURN
    DIVIDE ( COUNTROWS ( ReturningUsers ), COUNTROWS ( CurrentUsers ) )

[FreemanZ]

hi @chrisw888 

try to plot the line part with a measure like:

RepeatRate=

VAR _count=

DISTINCTCOUNT(TableName[UserID])

VAR _countrepeat =

COUNTROWS(

    INTERSECT(

        VALUES(TableName[UserID]),

            CALCULATETABLE(

            VALUES(TableName[UserID]),

            TableName[Date]=EDATE(MAX(TableName[Date]), -1)

             )

     )

)

RETURN

DIVIDE(_countrepeat, _count)