New Users Logging in for the First Time

DAX DAX Calculations
1

I have a list of Test Users and the Date they logged in to the System.
I want to show in a bar chat for each Day (1) the total users who logged in that day, a simply count or rows. In that same date I want to show the number of people who have never logged in, New Users. On a daily bases, compare new users to everyone who logged in. A new user is someone who logged in that day and no prior days.

eDNA New People.pbix (145.6 KB)
EDNA New People.csv (82.3 KB)

2

Hi @chad.sharpe - Check the results created by Data Mentor tool and do test it out.

DAX Formula for Calculating Daily Metrics

1. Total Users Logged In per Day:

Total Users Logged In = 
COUNTROWS('Test Users')

Explanation: This formula simply counts the total number of rows in the ‘Test Users’ table, which represents the total users who logged in on a specific day.

2. Number of New Users Logged In per Day:

New Users Logged In = 
VAR CurrentDate = MAX('Test Users'[Date])
RETURN
COUNTROWS(
    FILTER('Test Users',
        CALCULATE(MAX('Test Users'[Date]), ALLEXCEPT('Test Users', 'Test Users'[User])) = CurrentDate
    )
)

Explanation: This formula calculates the number of new users who logged in on a specific day by comparing the current date to the maximum date each user has logged in before. If the current date is the same as the user’s maximum login date, then they are considered new users for that day.

3. Daily Comparison of New Users to Total Users Logged In:

New Users Ratio = 
DIVIDE([New Users Logged In], [Total Users Logged In], 0)

Explanation: This formula calculates the ratio of new users to total users logged in on a specific day by dividing the number of new users by the total number of users. It handles the scenario where there are no total users logged in by returning a 0 in the denominator.

Practical Example

Suppose we have the following ‘Test Users’ table:

  • User1, Date: 01/01/2022
  • User2, Date: 01/01/2022
  • User3, Date: 02/01/2022
  • User4, Date: 02/01/2022
  • User5, Date: 02/01/2022

By applying the above DAX formulas, we can calculate the metrics for each day and compare new users to total users logged in.

This approach provides valuable insights into daily user engagement and the growth of new users over time.

By visualizing these metrics in a bar chart, you can easily monitor daily user trends and assess the effectiveness of user acquisition strategies.

Thanks
Ankit J

3

Interesting approach to compare the MAX Date. I was sort of on that line of thinking but was stuck on the approach. I’ll play with the structure suggested here and see if it produces the results needed…