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 

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 

    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 

    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

     

  • DusterD's avatar
    DusterD
    Frequent Visitor
    7 years ago

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

    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

  • DusterD's avatar
    DusterD
    Frequent Visitor
    7 years ago

    Thank you so much for the help Mariusz !