cancel
Showing results for
Did you mean:  Helper I

## Get users retained from previous day

I have a user activity tables that records every time a user opens that. It displays the user's ID and exactly when they opened the application (date).

Sample data:

 user_id date 1 01/01/2021 2 01/01/2021 2 02/01/2021 3 03/01/2021

So from the sample data, on 02/01/2021, only user 2 is retained since he opened the app the day before and on 02/01/2021.

The output will be visualized in a stacked column chart where the active users and the users retained on a particular day are highlighted (stacked on top of each other). Something like this: I want to calculate the number of unique users that have been retained from previous day, i.e, the users that opened that app the day before a date then again on that date.

How would I do that? It can be either a measure or calculated column.

1 ACCEPTED SOLUTION  Super User

@2JK

Active Users Measure:

``Active Users = COUNT(Table3[user_id])``

Retained Users Measure:

``````Retained Users =
VAR __CurrentDate = MAX(Table3[date])
VAR __CurrentUsers = VALUES(Table3[user_id])
VAR __PrevDate = CALCULATE( MAX(Table3[date]) , Table3[date] <  __CurrentDate )
VAR __PrevDayUsers = CALCULATETABLE( VALUES(Table3[user_id]) , Table3[date] = __PrevDate )
VAR __Result = COUNTROWS( INTERSECT( __CurrentUsers , __PrevDayUsers ) )
RETURN
__Result
``````

Result in Chart: Did I answer your question? Mark my post as a solution! and hit thumbs up
5 REPLIES 5  Super User

@2JK

Active Users Measure:

``Active Users = COUNT(Table3[user_id])``

Retained Users Measure:

``````Retained Users =
VAR __CurrentDate = MAX(Table3[date])
VAR __CurrentUsers = VALUES(Table3[user_id])
VAR __PrevDate = CALCULATE( MAX(Table3[date]) , Table3[date] <  __CurrentDate )
VAR __PrevDayUsers = CALCULATETABLE( VALUES(Table3[user_id]) , Table3[date] = __PrevDate )
VAR __Result = COUNTROWS( INTERSECT( __CurrentUsers , __PrevDayUsers ) )
RETURN
__Result
``````

Result in Chart: Did I answer your question? Mark my post as a solution! and hit thumbs up  Helper I

This worked exactly like I wanted. Much appreciated.

Just one question; in the formula for the previous date, would just subtracting 1 from the current date return the same thing?  Super User

@2JK

Yes, you can do but I did it this way if you do not have data for the previous day, it gets the last available date

Did I answer your question? Mark my post as a solution! and hit thumbs up  Super User

@2JK
Can you share some sample data with the desired output to have a clear understanding of your question?
Mention whether you want a calculated column or measure.
You can either paste your data in the reply box or save it in OneDrive, Google Drive, or any other cloud-sharing platform and share the link here.

Did I answer your question? Mark my post as a solution! and hit thumbs up  Helper I

I updated my post. Hope it's clearer.   