cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Max_Faro Frequent Visitor
Frequent Visitor

Active Users from last 7 days (low, medium, highly active)

I'm trying to calculate how many users have been active the last 1-2 days in the last 7 days, the last 3-5 days in the last 7 days and 6-7 days in the last 7 days relative to a date. I have a table with all logins users made to work with. The expected result would be something like this where for each day you have the number of low active users(1-2 days), medium active users(3-5 days) and high active users(6-7 days).

 

DateLow Active UsersMedium Active UsersHigh Active Users
01/01/201923105
02/01/201915127
03/01/2019453025
04/01/2019942
05/01/2019561611
06/01/2019322520
07/01/201915105
08/01/2019515045
09/01/2019463631
10/01/2019463833
11/01/2019222015
12/01/20191594

 

The login table looks like this:

IDDate
User101/01/2019
User201/01/2019
User301/01/2019
User401/01/2019
User501/01/2019
User601/01/2019
User701/01/2019
User402/01/2019
User502/01/2019
User602/01/2019
User702/01/2019
User203/01/2019
User303/01/2019
User403/01/2019
3 REPLIES 3
Community Support Team
Community Support Team

Re: Active Users from last 7 days (low, medium, highly active)

HI @Max_Faro ,

You can use following calculated column formula to check current user active count in last 7 days and mark tag:

Active =
VAR _count =
    CALCULATE (
        COUNT ( Test[Date] ),
        FILTER (
            ALL ( Test ),
            [Date]
                >= EARLIER ( Test[Date] ) - 7
                && [Date] <= EARLIER ( Test[Date] )
                && [ID] = EARLIER ( Test[ID] )
        )
    )
RETURN
    IF (
        _count >= 6,
        "High",
        IF ( _count >= 3 && _count <= 5, "Medium", IF ( _count < 3, "Low" ) )
    )

After these steps, you can use date as row fields, active as column fields and 'count of id' as value to achieve your requirement.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
Max_Faro Frequent Visitor
Frequent Visitor

Re: Active Users from last 7 days (low, medium, highly active)

Hi @v-shex-msft The problem here is that player can login many times per day and between this logins events are other events users perform. Therefore all users end up highlighted as High because of the number of logins they make each day.

Community Support Team
Community Support Team

Re: Active Users from last 7 days (low, medium, highly active)

Hi @Max_Faro ,

You can consider to apply distinct function on records to remove duplicate records. If you are confused on coding formula, please share pbix sample file and expected result for test.

 

Active =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( Test[Date] ),
        FILTER (
            ALL ( Test ),
            [Date]
                >= EARLIER ( Test[Date] ) - 7
                && [Date] <= EARLIER ( Test[Date] )
                && [ID] = EARLIER ( Test[ID] )
        )
    )
RETURN
    IF (
        _count >= 6,
        "High",
        IF ( _count >= 3 && _count <= 5, "Medium", IF ( _count < 3, "Low" ) )
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |