cancel
Showing results for
Search instead for
Did you mean:
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).

 Date Low Active Users Medium Active Users High Active Users 01/01/2019 23 10 5 02/01/2019 15 12 7 03/01/2019 45 30 25 04/01/2019 9 4 2 05/01/2019 56 16 11 06/01/2019 32 25 20 07/01/2019 15 10 5 08/01/2019 51 50 45 09/01/2019 46 36 31 10/01/2019 46 38 33 11/01/2019 22 20 15 12/01/2019 15 9 4

The login table looks like this:

 ID Date User1 01/01/2019 User2 01/01/2019 User3 01/01/2019 User4 01/01/2019 User5 01/01/2019 User6 01/01/2019 User7 01/01/2019 User4 02/01/2019 User5 02/01/2019 User6 02/01/2019 User7 02/01/2019 User2 03/01/2019 User3 03/01/2019 User4 03/01/2019
3 REPLIES 3
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: | |
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

## 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: | |