Get the new, lost, recovered users from a table containing user registration info using Power BI DAX

[fennyw29]

I have a table containing columns such as:

• user_id : One user only has one user_id
• reg_id : registration ID. One user can have more than one reg_id. User can delete their membership and re-register. Each time a user registered, they will get a different new reg_id.
• is_deleted : contains value 'yes' or 'no', to distinguish whether reg_id has been deleted.
• created_date: the date of registration.

I look forward to create measures in dax to obtain the current users, new users, lost users, and recovered users and create visualization similar to the one created here. However due to the very different nature of the table, I have difficulty following and improvising.

The following is the explanation of each term's definition in my case:

Existing users: the number of active users within that time period.

Count of distinct users in which the is_deleted = 'No'.

New users: the number of users who registers within that time period.
For each user_id, the created_date is within the time period. Ignore the is_delete column. Users who deleted their membership and reregister are considered new users according to the observed time period.

Lost users: the number of users whose account has been deleted before the start of the current period.
is_deleted = 'Yes' and created_date < current period date

Recovered users: the number of users who were considered lost in a previous time period, and then register in the current period.

Thank you. I appreciate your helpful ideas and answers.

[JoeBarry · Accepted solution]

Hi @fennyw29 

Recovered users will be difficult, is there an email address column or other columns where we can identify a person? 

Lets start on the other measures

Existing Users: here you will need to create a running total from  @Greg_Deckler 

Existing Users = 
VAR _Date = MAX('Table'[created_date])
VAR _Table = FILTER(ALLSELECTED('Table'),[created_date] <= _Date)
RETURN
    CALCULATE(SUMX(_Table,[reg_id]), FILTER('Table', 'Table'[is_deleted] = "no"))

New Users

New Users = CALCULATE(DISTINCTCOUNT('Table'[reg_id]), KEEPFILTERS([is_deleted] = "no"))

Lost Users is the same as above, just replace the no with yes.

You can then add these measures to a stacked bar chart value field. If you need to filter on each measure. Go to Modelling tab and choose New Parameter and then Fields add the measures to the fileds in the order you want to see them in the visual. and press create. This will create a new measure, add this instead to the stacked bar chart.

Let me know regarding my question with the recovered customers

Thanks

Joe

If this post helps, then please Accept it as the solution

[fennyw29]

Hi @JoeBarry, thank you for the fast response. In this table, the identifier for one person is their user_id which is unique for each person, is that not sufficient for this case?  I also have name, DoB, stuff like that but I'm not sure if that is unique enough to identify a person. EDIT: yes, the table contains email address too

[JoeBarry]

If I understand correctly, the user_id is seperate from the reg_id and then user will keep the original user_id when they re-register? 

Without knowing your data, you can try this 

Returning Users = 
COUNTROWS(
    FILTER(
        RegistrationTable,
        RegistrationTable[IsDeleted] = TRUE && 
        CALCULATE(
            COUNTROWS(RegistrationTable),
            FILTER(
                RegistrationTable,
                RegistrationTable[UserID] = EARLIER(RegistrationTable[UserID]) &&
                RegistrationTable[CreatedDate] > EARLIER(RegistrationTable[CreatedDate]) &&
                RegistrationTable[IsDeleted] = FALSE
            )
        ) > 0
    )
)

[fennyw29]

yes, correct. The user will keep their original user_id when they re-register. Thank you for the solution.

Thank you