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,

 

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'