Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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:

 

  

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.

Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

    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:

     

5 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Fowmy
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      I updated my post. Hope it's clearer.