A measure to count the users who had activity each day/week/month in the selected period

[1Francesco]

Hi community, 

Can you please advise me how I can count the number of users who have had activity each day, week or month in a selected period of time. 

I have a user login history table - UserLogins and a date table. 

UserLogins table:

LoginDwId	UserId	LoginDate	WeekNum	MonthNum	WeekUserId	MonthUserId
201044	U123	2022-09-16	202238	20229	U123202238	U12320229
201489	U124	2022-09-17	202238	20229	U124202238	U12420229
201488	U125	2022-09-18	202239	20229	U125202239	U12520229
201487	U126	2022-09-19	202239	20229	U126202239	U12620229
201486	U127	2022-09-20	202239	20229	U127202239	U12720229
201485	U128	2022-09-21	202239	20229	U128202239	U12820229
201484	U129	2022-09-22	202239	20229	U129202239	U12920229
201483	U130	2022-09-23	202239	20229	U130202239	U13020229
201482	U131	2022-09-24	202239	20229	U131202239	U13120229

As you can see, I created a new WeekUserId and MonthUserId by combining the UserId and the Week or Month IDs. My idea was to compare the unique number of WeekUserId and the unique number of weeks for the selected time period to see if the user had a login in each week of the selected period. Something like 

Active Users - Monthly Login = IF(DISTINCTCOUNT(UserLogins[MonthUserId])=DISTINCTCOUNT('Date'[Month]),1,0), but unfortunately this does not work. 

Can you please advise me what is the best way to calculate it.

Thank you in advance and have a nice day!

[v-luwang-msft · Accepted solution]

Hi @1Francesco ，

Test the below :

TEST = CALCULATE(DISTINCTCOUNT(Loguserid[UserId]),FILTER(ALL(Loguserid),Loguserid[MonthNum]=SELECTEDVALUE('date'[Month])))

The dax will get the  distinct activity user id according the month you selected:

Best Regards

Lucien

[1Francesco]

Hi singhsudha1301,

thanks a lot for your reply! Could you please describe in more detail how I could use this index to define if a selected user has been active every month in a selected time period?

Thank you! 
 

[singhsudha1301]

Hi @1Francesco,

You can use RANKX DAX function to create a column and use the ID columns you have to create it. Something like below:

Index = RANKX (FILTER ( Table,

        EARLIER ( Table[WeekUser_id] ) = Table[WeekUser_id]

            && EARLIER ( Table[EventDateMonthYear] ) = Table[EventDateMonthYear]),

    Table[event_date], ,ASC)

In this, event_date is the date column, EnentDateMonthYear is a column derived from event_date for month and year data, because I wanted to check the activity count in a month.

Please refer the RANKX document for peroper understanding. 

Hope this helps!

Thank you