RANKX doesn't reset on skipped months!

[NilR]

Start_Dates must be consecutive otherwise the count should be re-started. how can I do that? Thank!

Active_records_last_22_months = 
var _GP# = '0200 AW'[GP#]
var _id = '0200 AW'[CID]
var _end_date = '0200 AW'[Start_Date]
var _start_date = EOMONTH(_end_date,-24)+1

VAR _COUNT = RANKX ( 
        FILTER (ALL('TBL'),
            'TBL'[GP#] = EARLIER('TBL'[GP#])
            && '0200 AW'[CID] = EARLIER('TBL'[CID])
            && '0200 AW'[MALES_UNDER_35] = EARLIER('TBL'[MALES_UNDER_35])
            && '0200 AW'[Start_Date] >= _start_date
            && '0200 AW'[Start_Date] <= _end_date
            
        ),        
        '0200 AW'[Start_Date],
        ,
        ASC,
        Dense
    )
 RETURN 
 
_COUNT

GP#	CID	GNDR	AGE	MOS	WELL	Active_records_last_22_months	Result - needed	MALES_UNDER_35	Start_Date
23	425****	F	35-44	1	0	1	1	NO	03/01/2021
23	425****	F	35-44	1	0	2	2	NO	04/01/2021
23	425****	F	35-44	1	0	3	3	NO	05/01/2021
23	425****	F	35-44	1	0	4	4	NO	06/01/2021
23	425****	F	35-44	1	0	5	5	NO	07/01/2021
23	425****	F	35-44	1	0	5	5	NO	07/01/2021
23	425****	F	35-44	1	0	6	6	NO	08/01/2021
23	425****	F	35-44	1	0	6	6	NO	08/01/2021
23	425****	F	35-44	1	0	7	7	NO	09/01/2021
23	425****	F	35-44	1	0	8	8	NO	10/01/2021
23	425****	F	35-44	1	0	9	9	NO	11/01/2021
23	425****	F	35-44	1	0	10	10	NO	12/01/2021
23	425****	F	35-44	1	0	11	11	NO	01/01/2022
23	425****	F	35-44	1	0	12	12	NO	02/01/2022
23	425****	F	35-44	1	0	13	13	NO	03/01/2022
23	425****	F	35-44	1	0	14	14	NO	04/01/2022
23	425****	F	35-44	1	0	15	1	NO	09/01/2022
23	425****	F	35-44	1	0	16	2	NO	10/01/2022
23	425****	F	35-44	1	0	17	3	NO	11/01/2022
23	425****	F	35-44	1	0	18	4	NO	12/01/2022
23	425****	F	35-44	1	0	19	5	NO	01/01/2023
23	425****	F	35-44	1	0	20	6	NO	02/01/2023
23	425****	F	35-44	1	0	20	7	NO	03/01/2023
23	425****	F	35-44	1	0	20	8	NO	04/01/2023
23	425****	F	35-44	1	0	20	9	NO	05/01/2023
23	425****	F	35-44	1	0	20	10	NO	06/01/2023

[ryan_mayu]

@NilR 

you can create two columns 

Column = 
VAR _max=maxx(FILTER('Table','Table'[Start_Date]<EARLIER('Table'[Start_Date])),'Table'[Start_Date])
return if(EDATE('Table'[Start_Date],-1)=_max,0,1)


result = 
VAR _last=maxx(FILTER('Table','Table'[Start_Date]<=EARLIER('Table'[Start_Date])&&'Table'[Column]=1),'Table'[Start_Date])
VAR _sort=CALCULATE(DISTINCTCOUNT('Table'[Start_Date]),FILTER('Table','Table'[Start_Date]<=EARLIER('Table'[Start_Date])&&'Table'[Start_Date]>=_last))
return _sort

pls see the attachment below