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

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

  • 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



  • DusterD's avatar
    DusterD
    Frequent Visitor
    7 years ago

    Thank you so much for the help Mariusz !