Forum Discussion
Count new users based on usage logging
How can I create two column charts based on usage logging that shows the growth of users? I have the username and timestamp (among other metrics).
I want one chart to show the number of users that's never been seen before (new users), and one that shows the accumulated user count (all unique users).
Date, username
2019-01-01, user1
2019-01-01, user1
2019-01-24, user2 -- Two new users in Jan
2019-02-11, user1
2019-02-17, user3
2019-02-11, user2 -- Three unique, but only one new user i Feb
2019-03-05, user4 -- One new user i Mar
and so on...
Chart1, new users per month:
|
| | |
-----------------
j f m...
Chart2, total unique users:
|
| |
| | |
| | |
-----------------
j f m...
Thanks!
hi Anonymous
You could try this way as below:
Step1:
Add a YearMonth column for date column
Step2:
Use these two formula to create two measure for your requirement:
new users = CALCULATE(DISTINCTCOUNT('Table'[username]),FILTER('Table','Table'[Date]=CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[username]))))all unique users = CALCULATE(DISTINCTCOUNT('Table'[username]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))Result:
and here is sample pbix file, please try it.
Regards,
Lin
2 Replies
hi Anonymous
You could try this way as below:
Step1:
Add a YearMonth column for date column
Step2:
Use these two formula to create two measure for your requirement:
new users = CALCULATE(DISTINCTCOUNT('Table'[username]),FILTER('Table','Table'[Date]=CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[username]))))all unique users = CALCULATE(DISTINCTCOUNT('Table'[username]),FILTER(ALL('Table'),'Table'[Date]<=MAX('Table'[Date])))Result:
and here is sample pbix file, please try it.
Regards,
Lin