Active userbase calculation

[Anonymous]

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?

[v-yiruan-msft]

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

[Anonymous]

Hi @v-yiruan-msft,

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?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

I created sample pbix file, you can get it from this link.

Best Regards

Rena

[mahoneypat]

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

[amitchandak]

@Anonymous , refer to my blog on Week, Try to have measure rolling 4 or 5 week, based on the 12 week rolling measure

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123