Count number of active users

[Isildur13]

Hi all

I need help with creating either a DAX or PQ calculations that can give me the number of registered users.

The issue is that a user can be registered multiple times before the system recognize it as a registered member. Therefore the business logic states that if a user has multiple registerdates within a 30day period it should only be counted as 1 registration.

My data would look something like below:

UserID	RegisterDate
1	01-10-2023
1	06-10-2023
2	15-06-2023
3	19-03-2023
3	11-09-2023
4	01-09-2023
4	03-09-2023
4	04-09-2023

And the result I am looking for should be like this:

October = 1

September = 2

June = 1

March = 1

[gmsamborn · Accepted solution]

Hi @Isildur13 

Maybe try this measure.

zMeasure = 
VAR _Table1 =
    SUMMARIZE(
        'Date',
        'Date'[Month],
        "Users",
            DISTINCTCOUNT( 'Table1'[UserID] )
    )
VAR _Result = 
    SUMX(
        _Table1,
        [Users]
    )
RETURN
    _Result

Also, based on your description and your expected results, this sounds more like "New users by month" instead of "active users"  as you stated in the title of your thread. 

Please let me know if I've misunderstood anything.

Count of NEW users.pbix

[gmsamborn · Accepted solution]

Hi @Isildur13 

Maybe try this measure.

zMeasure = 
VAR _Table1 =
    SUMMARIZE(
        'Date',
        'Date'[Month],
        "Users",
            DISTINCTCOUNT( 'Table1'[UserID] )
    )
VAR _Result = 
    SUMX(
        _Table1,
        [Users]
    )
RETURN
    _Result

Also, based on your description and your expected results, this sounds more like "New users by month" instead of "active users"  as you stated in the title of your thread. 

Please let me know if I've misunderstood anything.

Count of NEW users.pbix

[Isildur13]

Hi @gmsamborn 

It worked and I added the Year as a column as well 🙂

One follow up question - would it also be possible if I instead would use a rolling 40 day instead of the month?

[gmsamborn · Accepted solution]

Hi @Isildur13 

Offhand, I can think of an easy way to do that.

The "best" way I can I think of would be to incorporate this period into your date table.  It could be calculated based on a day-offset column if your date table has that.  (If not, I reccommend it.)

I hope this make sense.  Let me know if you want an example of a date table with offset columns.

[barritown]

Hi @Isildur13,

I am not sure my solution is optional but it should do the trick for you:

In plain text for convenience:

ResTable = 
VAR _tbl1 = ADDCOLUMNS ( Data, "Month", FORMAT ( [RegisterDate], "MMMM" ) )
VAR _tbl2 = SUMMARIZE ( _tbl1, [Month], 
                        "Counter", 
                        VAR CurMonth = [Month]
                        RETURN COUNTX ( DISTINCT ( SELECTCOLUMNS ( FILTER ( _tbl1, [Month] = CurMonth ), "User", [UserID] ) ), [User] ) )
RETURN _tbl2

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian