SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t
    (memberid int, yearmonth int, activestatus varchar(1))
;
    
INSERT INTO t
    (memberid, yearmonth, activestatus)
VALUES
    (1, 202201, 'Y'),
    (1, 202202, 'Y'),
    (1, 202203, 'N'),
    (2, 202201, 'N'),
    (2, 202202, 'N'),
    (2, 202203, 'Y'),
    (3, 202201, 'N'),
    (3, 202202, 'Y'),
    (3, 202203, 'Y')
;
9 rows affected

select * from t
 order by yearmonth


memberid
yearmonth
activestatus


1
202201
Y

2
202201
N

3
202201
N

3
202202
Y

2
202202
N

1
202202
Y

1
202203
N

2
202203
Y

3
202203
Y


select distinct yearmonth                 
      ,count(rn) over(order by yearmonth) as distinct_running_count
from
(
select *
      ,case when row_number() over(partition by memberid order by yearmonth) = 1 then 1 end as rn
from   t
where  activestatus = 'Y'
) t


yearmonth
distinct_running_count


202201
1

202202
2

202203
3


Warning: Null value is eliminated by an aggregate or other SET operation.

memberid	yearmonth	activestatus
1	202201	Y
2	202201	N
3	202201	N
3	202202	Y
2	202202	N
1	202202	Y
1	202203	N
2	202203	Y
3	202203	Y