Calculating active users based on the last 6 months

[DusterD]

Hello,

I've been searching for a solution for this for quite a while, I need to calculate active users based on a 6month window. It would have to go back 6months from the latest month(or month selected by a slicer). To determine if a user is active they have to have 3 or more occurrences in the previous 6 months. I have a column for case ID, user ID and month submitted. Any help would be greatly appreciated.

[Mariusz · Accepted solution]

Hi @DusterD 

Here try this.

Past 6 months active = 
CALCULATE(
    INT( DISTINCTCOUNT( Table1[CASE_ID] ) >= 3 ),
    DATESINPERIOD( Table1[DATE_SUBMITED].[Date], MAX( Table1[DATE_SUBMITED].[Date] ), -6, MONTH )
)

• DISTINCTCOUNT will count distinct Case ID, if no duplicates you can replace it with COUNTROWS( Table1 )
• >= 3 will convert the count to true \ false 
•  INT() will convert it to 1/0, so 1 is for active customer with 3 and over 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[Mariusz]

Hi @DusterD 

Can you provide a data sample?

Best Regards,
Mariusz

Please feel free to connect with me.

[DusterD]

Hello Mariusz,

Thanks for the reply! Here's a sample :

[DusterD]

To clarify, for a user ID to be active, they must have 3 case IDs in the last 6 months. All case IDs are unique.

[Mariusz]

Hi @DusterD 

You can try the below if it works for you.

Past 6 months active = 
CALCULATE(
    INT( DISTINCTCOUNT( Table1[CASE_ID] ) > 3 ),
    DATESINPERIOD( Table1[DATE_SUBMITED], MAX( Table1[DATE_SUBMITED] ), -6, MONTH )
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DusterD]

Hi Mariusz,

I tried your measure and it won't display the visual. Also, I'm not understanding how I'd get a count of active users in your measure?

Error:

A date column containing duplicate dates was specified in the call to function 'DATESINPERIOD'

I also tried this measure:

Active = CALCULATE(

COUNT(Table1[User_ID]), Table1[CASE_ID] >=3,

DATESINPERIOD(Table1[DATE_SUBMITTED], MAX(Table1[DATE_SUBMITTED]), -6, MONTH))

Error:

DAX comparison operations do not support comparing values of type text with values of type interger.

[Mariusz · Accepted solution]

Hi @DusterD 

Here try this.

Past 6 months active = 
CALCULATE(
    INT( DISTINCTCOUNT( Table1[CASE_ID] ) >= 3 ),
    DATESINPERIOD( Table1[DATE_SUBMITED].[Date], MAX( Table1[DATE_SUBMITED].[Date] ), -6, MONTH )
)

• DISTINCTCOUNT will count distinct Case ID, if no duplicates you can replace it with COUNTROWS( Table1 )
• >= 3 will convert the count to true \ false 
•  INT() will convert it to 1/0, so 1 is for active customer with 3 and over 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DusterD]

I appreciate the help Mariusz,

I tried that measure and it's returning 0 for every row, I'm assuming that since all CASE_ID are distinct values it will never reach 3, even using COUNTROWS. Also where does USER_ID fall into this? Will I create another measure to count USER_ID based on the value 1 from this measure?

[Mariusz]

Hi @DusterD 

Please see the below.

data used for this scenario.

CASE_IDUSER_IDDATE_SUBMITED

1	1	03/06/2019 00:14:00
2	1	03/07/2019 00:14:00
3	1	04/04/2019 00:14:00
4	1	02/06/2019 00:14:00
5	1	01/06/2019 00:14:00
6	1	03/06/2019 00:14:00
7	1	03/06/2019 00:14:00
8	3	03/06/2019 00:14:00
9	3	01/06/2019 00:14:00
10	2	04/06/2019 00:14:00
11	2	05/06/2019 00:14:00
12	2	03/01/2019 00:14:00
13	2	03/01/2019 00:14:00
14	2	03/01/2019 00:14:00
15	2	03/01/2019 00:14:00
16	2	03/01/2019 00:14:00

Please see the below screenshot.
Based on the sample above,  in Jun 2019, user 1 and 2 were active because they have raised 3 or more cases with in 6 months Jan to June 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DusterD]

Hi @Mariusz 

This works, however I'm getting a number that's too high. Could this be caused by the fact that this report islooking at December to May? Since it's changing from 2018 to 2019?

[DusterD]

Hi @Mariusz 

Digging deeper, I created measure to get a distinctcount user IDs and when I select the 6 months in the slicer the number is exactly what I'm looking for. My end goal here is to be able to have a count of active users per month that I can graph. Thanks so much for your help!

Correct number with 6 months selectedToo low when latest month selectedToo high when all months selected

[Mariusz · Accepted solution]

Hi @DusterD 

The below will count the active users

Past 6 months active = 
VAR _users = 
CALCULATETABLE( 
    GROUPBY( 
        Table1, 
        Table1[USER_ID],
        "CaseCount", COUNTX( CURRENTGROUP(), Table1[CASE_ID] )
    ),
    DATESINPERIOD( Table1[DATE_SUBMITED].[Date], MAX( Table1[DATE_SUBMITED].[Date] ), -6, MONTH )
) 
RETURN COUNTROWS( FILTER( _users, [CaseCount] > 2 ) )

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[DusterD]

Thank you so much for the help @Mariusz !

[DusterD]

I changed the data type for CASE_ID to be decimal number and am now getting the same error for both measures:

A date column containing duplicate dates was specified in the call to function 'DATESINPERIOD'