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 

Can you provide a data sample?

 

 

Best Regards,
Mariusz

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