Unique records per month over series of months

[Anonymous]

Hi,

I have a table with a user ID and all the dates a user logged in

User ID	Log in date
A	Jan 1 2019
A	Jan 2 2019
A	Feb 20 2019
A	Mar 10 2019
A	Apr 15 2019
B	Jan 5 2019
B	Mar 6 2019
B	Apr 12 2019

I’m now looking for a measure that will show me the amount of unique users that logged in at least once per month over a series of months that I define using a filter on the page.

Example:

I set the filter on the page to April 2019. The measure should then show me that there’s 2 users (A and B) that logged in once for April.

I set the filter on the page to January, February, March, and April 2019. User A logged in every month over that time period. User B only logged in in January March and April, and NOT in February. So in this case the measure should return 1 as only user A logged in at least once for all of those months.

So as you can see a simple distinctcount wouldn’t work here. What measure should I use to get the desired outcome?

Thanks in advance!

[Mariusz]

Hi @Anonymous 

Please see the below, let me know if this is what your after.

Measure = 
CONCATENATEX(
    VALUES(YourTable[User ID]),
    YourTable[User ID],
    ", "
)

Regards,
Mariusz

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

[Anonymous]

Hi @Mariusz ,

Thank you for helping out. your calculation is returning an enormous long metric that looks like this

A, B, C, D, E, F

I need to know the unique amount of ID's that are in that metric, do you know how I can get that number?

Regards

Bas

[Mariusz]

Hi @Anonymous 

Not sure what you want to achieve, can you illustrate the desired output?

Thanks
Mariusz

[Anonymous]

Hi @Mariusz 

Sure thing.

1) I want this measure to show me the amount of users that logged in at least once per month between january and april 2019.

User A logged in in January, February, March, and April, and therefore counts as 1

User B logged in in January, March, and April. He did not log in in February. He therefore doesnt count.

In total I have 1 user that logged in at least once per month between january and april 2019, the measure should return 1.

2) I want the same measure to show me the amount of users that logged in at least once per month between March and April. 

User A logged in in March and April and therefore counts as 1

User B logged in in March and April and therefore counts as 1

In total I have 2 users that logged in at least once per month between March and April, the measure should return 2.

Does this make sense?

Best regards and thanks again for looking into this!

Bas

[Mariusz]

Hi @Anonymous 

Working on assumption that you have Year Month column in your table, the code should look something like below.

Measure = 
VAR ym = DISTINCTCOUNT( YourTable[YearMonth] ) 
RETURN 
COUNTROWS(
    FILTER( 
        VALUES( YourTable[User ID] ), 
        CALCULATE( DISTINCTCOUNT( YourTable[YearMonth] ) ) = ym 
    )
)

Regards,
Mariusz

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