Forum Discussion
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 selected by a slicer). To determine if a user is active they have to have 3 or more occurrences in the previous 6 months. I have a column for case ID, user ID and month submitted. Any help would be greatly appreciated.
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.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.
