DISTINCTCOUNT if id each every month.

[Anonymous]

Hello
I have a 6 moths data. It's web logins. I need a measure, where i could calculate HOW UNIQUE USERS LOGINS EVERY MONTH, and if this users logins every month - return count, and if not logins every moth - not return. 
For example

user_id	login_date	web/app
1	2022-05-05	web
2	2022-05-05	web
3	2022-05-05	web
4	2022-05-05	app
1	2022-06-05	app
5	2022-06-05	web
3	2022-06-05	app
1	2022-07-05	web
6	2022-07-05	web
3	2022-07-05	web
1	2022-08-05	app
6	2022-08-05	web
7	2022-08-05	web

And this i need:

Date	Unique users all	Unique u. per period	login every month
month 5	4	4
month 6	5	3	2
month 7	6	3	2
month 8	7	3	1

Thank you. I not have idea. 😞 

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Unique users all = CALCULATE(MAX('Table'[user_id]),ALLEXCEPT('Table','Table'[login_date]))

Unique u. per period = CALCULATE(COUNT('Table'[web/app]),ALLEXCEPT('Table','Table'[login_date]))

Measure =
CALCULATE (
    COUNT ( 'Table'[user_id] ),
    FILTER (
        ALL ( 'Table' ),
        [user_id] = SELECTEDVALUE ( 'Table'[user_id] )
            && [login_date] <= SELECTEDVALUE ( 'Table'[login_date] )
    )
)

login every month =
CALCULATE (
    COUNT ( 'Table'[user_id] ),
    FILTER (
        ALL ( 'Table' ),
        [Measure] >= 2
            && [login_date] = SELECTEDVALUE ( 'Table'[login_date] )
    )
)

Is this the result you expect? If I misunderstand, please provide more explanation.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.