I need to Rank based on Multiple columns but for some IDs I have gap in [Date] for example for One ID I have 202209 and the next DateMonth is 202305 and I need to restart when the gap is more than 1 Month.
-- Calc Column
Active_records =
var _GP = Table[GP]
var indv_id = Table[ID]
var _end_date = Table[DATE]
var _DATE = EOMONTH(_end_date,-24)+1
VAR _COUNT = RANKX (
FILTER (ALL(Table),
Table[GP] = EARLIER(Table[GP])
&& Table[ID] = EARLIER(Table[ID])
&& Table[MALES_UNDER_35] = EARLIER(Table[MALES_UNDER_35])
&& Table[DATE] >= _DATE
&& Table[DATE] <= _end_date
),
Table[DATE],
,
ASC,
Dense
)
RETURN
_COUNT
Thank you!