Counting active users, joiners & leavers

johan-svitla 

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.