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



DusterD's avatar
DusterD
Frequent Visitor
7 years ago

Hello Mariusz,

 

Thanks for the reply! Here's a sample :

 

DusterD's avatar
DusterD
Frequent Visitor
7 years ago

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'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'