I have 4 Categories (GP, ID, Age, Date). I would would like to create calculated column and group by GP, ID, and Age and Rank/ count by Date to see how many months each member has in past 24 month.
My Code works until I have members who cancelled their membership for a few months and then resumed after. I need to restart from the first month after skip. for example :
| GROUP | ID | AGE | DATE | Current Rank | Desired RANK |
| 1 | 220 | 35-44 | 202206 | 12 | 6 |
| 1 | 220 | 35-44 | 202205 | 12 | 5 |
| 1 | 220 | 35-44 | 202204 | 12 | 4 |
| 1 | 220 | 35-44 | 202203 | 12 | 3 |
| 1 | 220 | 35-44 | 202202 | 12 | 2 |
| 1 | 220 | 35-44 | 202201 | 12 | 1 |
| 1 | 220 | 35-44 | 202012 | 24 | 24 |
| 1 | 220 | 35-44 | 202011 | 23 | 23 |
| 1 | 220 | 35-44 | 202010 | 22 | 22 |
| 1 | 220 | 35-44 | 202009 | 21 | 21 |
| 1 | 220 | 35-44 | 202008 | 20 | 20 |
| 1 | 220 | 35-44 | 202007 | 19 | 19 |
| 1 | 220 | 35-44 | 202006 | 18 | 18 |
| 1 | 220 | 35-44 | 202005 | 17 | 17 |
| 1 | 220 | 35-44 | 202004 | 16 | 16 |
| โฆ | โฆ | โฆ | โฆ | โฆ | โฆ |
| 1 | 220 | 35-44 | 201901 | 1 | 1 |
This is what I have tried but doesn't work for dates skipping.
RKING=
RANKX (
CALCULATETABLE (
VALUES ('tbl'[Date] ),
ALLEXCEPT ( 'tblW', 'tbl'[GP], 'tbl'[ID] ),
'tbl'[AGE] = 'tbl'[AGE],
'tbl'[date] >= start_date && 'tbl'[date] <= end_date // date slicer
),
[Date] ,
,ASC
)
