Calculate daily and monthly active users based on last login date

[Anonymous]

Hi all!

I am looking to use DAX to calculate daily active users (total number of users that logged in on any given day), and monthly active users (total number of users that logged in during the last 30 days, to be calculated on any given day).  

I have a user table with lots of information, including user id: Users[user id] and last login date: User[Login Date].  This is connected to a calendar dimension table.  

I'm new to Power BI & DAX, and have tried searching for a few days, but can't quite crack this one.

Any ideas?

Thanks in advance,

Matt

[Anonymous · Accepted solution]

@Icey  - thanks for your response!

It appears the DAU works, but MAU should be the sum of the users active over the past 30 days at any given date.  I filtered the file to November 4th (the last date with data loaded), and the MAU should be 576.  However, it appears to be laying values out on future dates, the sum of which is 576.  The MAU on November 4th should equal 576.

Additionally, in the full file, the table containing login date also contains a registration date.  When I implement the formula for DAU, it disrupts formulas already in place to measure new user registrations and total users registrations.  It actually makes DAU = new user registrations.

Thoughts...?

Thanks for your additional help in advance!

[Icey]

Hi @Anonymous ,

I create a simple example:

UserID	LoginDate
User1	1/1/2019
User2	1/1/2019
User3	1/1/2019
User4	1/2/2019
User1	1/2/2019
User2	1/2/2019
User1	1/3/2019
User2	1/3/2019
User1	1/4/2019
User3	1/4/2019
User4	2/1/2019

And create measures like so:

daily active users = DISTINCTCOUNT(user[UserID])+0

monthly active users =
CALCULATE (
    DISTINCTCOUNT ( user[UserID] ) + 0,
    DATEADD ( 'calendar'[Date], -30, DAY )
)

Is this what you want?

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

@Icey  - thanks for your response!

It appears the DAU works, but MAU should be the sum of the users active over the past 30 days at any given date.  I filtered the file to November 4th (the last date with data loaded), and the MAU should be 576.  However, it appears to be laying values out on future dates, the sum of which is 576.  The MAU on November 4th should equal 576.

Additionally, in the full file, the table containing login date also contains a registration date.  When I implement the formula for DAU, it disrupts formulas already in place to measure new user registrations and total users registrations.  It actually makes DAU = new user registrations.

Thoughts...?

Thanks for your additional help in advance!

[lc_finance · Accepted solution]

Hi @Anonymous ,

you can download my proposed solution from here.

Here is the new formula for the MAU:

MAU = 

var currentDate = SELECTEDVALUE('Date Table'[Date])  
var date30DaysAgo = FIRSTDATE(DATEADD('Date Table'[Date].[Date], -30, DAY))

RETURN 
 CALCULATE(
    DISTINCTCOUNT('Users'[User ID]) 
     ,'Date Table'[Date] <=currentDate,
     'Date Table'[Date] > date30DaysAgo)

And here is a screenshot with MAU at 576 on November 4th, as you expected.

I also modified the formula for New users based on what you mentioned below.

The new formula is:

New Users = 
CALCULATE(DISTINCTCOUNT('Users'[User ID]), USERELATIONSHIP('Date Table'[Date],'Users'[Registration Date]))

In this model, the relationship between the Date table and the User table is based on the column Login date. This allows us to count the users by Login date.

USERRELATIONSHIP activates a different relationship only for the calculation of New Users. For the calculation of New Users, it activates a relationship that uses the Registration date.

I hope that this is what you are looking for.

Let me know if you need more help!

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com 

[Anonymous]

@lc_finance 

This worked!  MAU is now correct.  It took me ~30 minutes to figure out why I was getting an error for the new formula for new users.  I could tell something was different between the formatting of the "login date" and "registration date" values, and did not realize that you had created a second relationship between the two tables.  Once I did that, everything appears to be working correctly!

Thank you so much for your help!

@Icey - thank you too!Original formula errorDifferent in formattingSecond relationship added

[lc_finance]

Hi @Anonymous ,

You are right! I'm sorry I did not mention that.

That said, I am very glad that it's working now for you!

LC

[lc_finance]

Hi @Anonymous ,

I'd be happy to help you with this.

Can you share a Power BI file with few lines of sample data, and an example of the numbers you expect to find on the report?

Regards

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.co

[Anonymous]

@lc_finance 

I cannot share my file, as it contains personal information.

What's the best way to tackle this?

[lc_finance]

Hi @Anonymous ,

can you replace the personal data with sample/fake data?

Seeing your file is indeed the best way to find a solution for your problem

LC

[Anonymous]

@lc_finance 

Let's give this a shot - I've stripped out the personal info for GDPR reasons

https://abb-my.sharepoint.com/:u:/p/matthew_wolford_us/EY9dj3mVYZFFp1A2-51LurUB0k3_6fgW2mpbUTLPL8o5S...