DAX - Rankx by multiple Categories Issue

Question

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 :

GP  ID  AGE     DATE    RKING 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 Column= 
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
)

Answer 1

Looking through the code you were trying to make a measure for a visual (For a calcCol the measure is added as well). And as I got a point, you want to show a sum of consequtive months in a matrix for each date in accordance to ID/GP/AGE/DATE I see a following way. As you know, calculations performs for each row in a matrix and filter the data model according to data presented in matrix rows and columns (slicers as well). So, my idea is -

1. Get date from matrixRow and use it as max date for the table.
2. Then use a FILTER(). FILTER() is an iterative function, so it goes throw each row and checks filtering condition - if true row remains if false - not.

I use following filtring conditions:

Get dateInMatrix-dateInACurrentTableRow (for example: 202203-202201= 2 months) Then check how many rows in the table with min=202201 and max<202203 if there are less rows then date difference then it FALSE() and the row is out of table. 3) The last step is counting of rows it a filtered table.

A measure for matrix:

Ranking = 
VAR matrixDate=MAX('table'[DATE])
VAR filteredTable = 
         FILTER(
            ALL('table')
            ,DATEDIFF(
                DATE(LEFT([DATE],4),RIGHT([DATE],2),1)
                ,DATE(LEFT(matrixDate,4),RIGHT(matrixDate,2),1)
                ,MONTH
            )
            =
                VAR dateInRow=[DATE]
                RETURN
                    CALCULATE(
                        COUNTROWS('table')
                        ,'table'[DATE]>=dateInRow
                        ,'table'[DATE]<matrixDate
                    )
        )
RETURN
    COUNTROWS(filteredTable)

[![enter image description here][1]][1]

A measure for calcColl:

RankColl = 
VAR currentDate=[Start_Date]
Var MyFilt={('Table'[AGE],'Table'[ID],'Table'[GROUP])}
VAR withColl = 
         ADDCOLUMNS(
            CALCULATETABLE(
                'table'
                ,ALL('Table')
                ,TREATAS(MyFilt,'Table'[AGE],'Table'[ID],'Table'[GROUP])
            )
            ,"dateDiff",
                        DATEDIFF(
                            [Start_Date]
                            ,currentDate
                            ,MONTH
                        )
            ,"RowsInTable",
                VAR dateInRow=[Start_Date]
                Var startDate=IF(dateInRow<currentDate,dateInRow,currentDate)
                VAR endDay =IF(dateInRow>currentDate,dateInRow,currentDate)
                VAR myDates = GENERATESERIES(startDate,endDay,1)
                RETURN
                    COUNTROWS(
                        CALCULATETABLE(
                            'Table'
                            ,ALL('Table')
                            ,TREATAS(MyFilt,'Table'[AGE],'Table'[ID],'Table'[GROUP])
                            ,TREATAS(myDates,'Table'[Start_Date])
                        )
                    )
        )
VAR filtered =
        FILTER(
            withColl
            ,[dateDiff]=[RowsInTable]-1  -- for ex.:
                                         -- dateDiff=01/01/2022-01/01/2022=0, 
                                         -- but it will be 1 row in the table for 01/01/2022
        )
RETURN
    CountRows( filtered)