Get users retained from previous day

[Anonymous]

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.

[Fowmy · Accepted solution]

@Anonymous 

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:

[Anonymous]

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?

[Fowmy]

@Anonymous 

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

[Fowmy]

@Anonymous 
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.

[Anonymous]

I updated my post. Hope it's clearer.