Forum Discussion

DusterD's avatar
DusterD
Frequent Visitor
7 years ago
Solved

Calculating active users based on the last 6 months

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 se...
Need Help
  • Mariusz's avatar
    Mariusz
    7 years ago

    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 Repczynski

     

  • Mariusz's avatar
    Mariusz
    7 years ago

    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.
    Mariusz Repczynski



Mariusz's avatar
Mariusz
Icon for Community Champion rankCommunity Champion
7 years ago

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.
Mariusz Repczynski

 

DusterD's avatar
DusterD
Frequent Visitor
7 years ago

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.
  • DusterD's avatar
    DusterD
    Frequent Visitor
    7 years ago

    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'

  • Mariusz's avatar
    Mariusz
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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 Repczynski

     

    • DusterD's avatar
      DusterD
      Frequent Visitor
      7 years ago

      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's avatar
        Mariusz
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi DusterD 

        Please see the below.

         

        data used for this scenario.

        CASE_IDUSER_IDDATE_SUBMITED

        1103/06/2019 00:14:00
        2103/07/2019 00:14:00
        3104/04/2019 00:14:00
        4102/06/2019 00:14:00
        5101/06/2019 00:14:00
        6103/06/2019 00:14:00
        7103/06/2019 00:14:00
        8303/06/2019 00:14:00
        9301/06/2019 00:14:00
        10204/06/2019 00:14:00
        11205/06/2019 00:14:00
        12203/01/2019 00:14:00
        13203/01/2019 00:14:00
        14203/01/2019 00:14:00
        15203/01/2019 00:14:00
        16203/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.
        Mariusz Repczynski