Forum Discussion
Counting active users, joiners & leavers
Hello johan-svitla
How would you define current month, Will you be using a slicer?
Also if start_date & end_date falls on current month, What would be the status.
Regards,
Naveen
Hi. Yes, the slicer would be my choice. If someone joined and left in the same month should be counted in both joiners and leavers.
Regards and thanks for your interest
johan-svitla
Id 4356546 has two entries, can you let me know what should be the desired output in such cases?Hi, this is a mistake on the sample data. IDs are unique and are never duplicated under any circumstance
Please try the below solution, This uses a combination of PQ and DAX.
Unpivot your table to below structure, as this is important to visuals leavers along with other two metric.Create below three measures, Use a disconnected calendar table for slicer and alter the measure accordingly.
Joiners =
CALCULATE (
COUNT ( Active[ID ] ),
FILTER (
Active,
IF (
AND (
MAX ( 'Calendar'[Month] ) = MONTH ( MAX ( Active[Date] ) ),
MAX ( 'Calendar'[year] ) = YEAR ( MAX ( Active[Date] ) )
),
1,
0
) = 1
),
Active[Type] = "Start Date"
)
Leavers =
CALCULATE (
COUNT ( Active[ID ] ),
FILTER (
Active,
IF (
AND (
MAX ( 'calendar'[Month] ) = MONTH ( MAX ( Active[Date] ) ),
MAX ( 'Calendar'[year] ) = YEAR ( MAX ( Active[Date] ) )
),
1,
0
) = 1
),
Active[Type] = "End Date"
)
Active =
VAR value_ =
LOOKUPVALUE (
'Calendar'[Date],
'Calendar'[Date], SELECTEDVALUE ( Active[Date] )
)
RETURN
CALCULATE (
COUNT ( Active[ID ] ),
FILTER (
Active,
IF (
AND (
CALCULATE ( COUNTROWS ( Active ), ALLEXCEPT ( Active, Active[ID ] ) ) = 1,
MONTH ( value_ ) < SELECTEDVALUE ( 'Calendar'[Month] )
&& YEAR ( value_ ) <= SELECTEDVALUE ( 'Calendar'[Year] )
),
1,
0
) = 1
)
)
Now use the date column and these three measure to visualize as below.Let me know if this helps.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.