cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
2JK
Helper I
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_iddate
101/01/2021
201/01/2021
202/01/2021
303/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:

 

2JK_0-1634468837464.png

  

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
Fowmy
Super User
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:

Fowmy_0-1634471689862.png

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

5 REPLIES 5
Fowmy
Super User
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:

Fowmy_0-1634471689862.png

 

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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

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?

@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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy
Super User
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


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

I updated my post. Hope it's clearer. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.