Forum Discussion
Active userbase calculation
Hi Community Members,
Have been wrapping my brain around this one, but can't seem to figure this one out on my one. I have a basic flat table with login behavior. A timestamp of login and which user logged in [user_id].
| timestamp | user_id | |
| 19-5-2020 10:08 | 80 | |
| 21-6-2020 18:21 | 648 | |
| 5-6-2020 09:07 | 307 | |
| 19-5-2020 09:54 | 130 | |
| 8-6-2020 15:43 | 28 | |
| 1-6-2020 12:59 | 425 |
What I am looking for is a way to calculate the amount of active users we have in our userbase at a certain point in time. The defintion for an active user is a distinctcount of user_id's for users that have logged in for four consecutive weeks prior to the week I am looking at. I have a seperate DATE table with a weeknumber in it.
Hence if I select week 27 in my DATE table, I would like to have a distinctcount of unique user_id's of users that logged in in week 23 & 24 & 25 & 26. However, if I select week 20, the previous weeks would be 16 & 17 & 18 & 19.
I have tried looking into this with a colleague, but have not figured out a measure yet.
Is someone able to give me a little nudge in the right direction?
5 Replies
Anonymous , refer to my blog on Week, Try to have measure rolling 4 or 5 week, based on the 12 week rolling measure
Please try this expression for the count of users that were active in all of the previous four weeks.
Active Users = VAR __thisweek = MIN ( 'Date'[WeekNumber] ) VAR __summary = FILTER ( CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZE ( Login, 'Date'[WeekNumber], Login[user_id] ), "@logins", CALCULATE ( COUNTROWS ( Login ) ) + 0 ), ALL ( 'Date'[WeekNumber] ), 'Date'[WeekNumber] < __thisweek && 'Date'[WeekNumber] >= __thisweek - 4 ), [@logins] > 0 ) VAR __usersummary = FILTER ( SUMMARIZE ( __summary, Login[user_id], "@weeks", COUNTROWS ( __summary ) ), [@weeks] = 4 ) RETURN COUNTROWS ( __usersummary )If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi Anonymous ,
You can create a measure as below:
Active userbase = CALCULATE ( DISTINCTCOUNT ( 'Userbase'[user_id] ), FILTER ( 'Userbase', WEEKNUM ( 'Userbase'[timestamp] ) >= SELECTEDVALUE ( 'DATE'[Weeknumber], WEEKNUM ( TODAY () ) ) - 4 && WEEKNUM ( 'Userbase'[timestamp] ) < SELECTEDVALUE ( 'DATE'[Weeknumber], WEEKNUM ( TODAY () ) ) ) )Best Regards
Rena
Hi Anonymous,
Out of all possible solutions, your solution gives me back some data. However, I do have two comments:
1. What happens when we move into the new year. Does the calculation still hold for week 2 of 2021, where I would like to see data for week 50 - 52 [2020] and week 1 of 2021?
2. When I make a card. It gives me back the number I am expecting. However, When I add weeknumber on the x-axis and I wish to see the trend per week, it simply gives me back the user count of that week. What I was expecting is that it would give back the distinct user count of the four weeks before week 26 [week 22 - 25]. And when I look at the week before that week 25 [week 21 - 25]. This is not what is happening. Any idea what might be happening?
