DAX Issue- Rankx by multiple Categories

[NilR]

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
)

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached file.

I hope this can provide some ideas on creating a solution to your problem.

step one Previousmonthmembership: = 
VAR _currentdate =
    MAX ( Data[DATE] )
VAR _currentgroup =
    MAX ( Data[GROUP] )
VAR _currentid =
    MAX ( Data[ID] )
VAR _currentage =
    MAX ( Data[AGE] )
RETURN
    MAXX (
        FILTER (
            ALLSELECTED ( Data ),
            Data[GROUP] = _currentgroup
                && Data[ID] = _currentid
                && Data[AGE] = _currentage
                && Data[DATE] < _currentdate
        ),
        Data[DATE]
    )

step two currentgroupindex: = 
VAR _currentdate =
    MAX ( Data[DATE] )
VAR _currentgroup =
    MAX ( Data[GROUP] )
VAR _currentid =
    MAX ( Data[ID] )
VAR _currentage =
    MAX ( Data[AGE] )
VAR _addindexcolumn =
    ADDCOLUMNS (
        ADDCOLUMNS (
            ALLSELECTED ( Data ),
            "@memberpreviousmonth", [step one Previousmonthmembership:]
        ),
        "@consecutivecondition",
            SWITCH (
                TRUE (),
                [@memberpreviousmonth] = BLANK (), 0,
                Data[DATE] - [@memberpreviousmonth] > 31, 1,
                0
            )
    )
VAR _addgroupingcolumn =
    ADDCOLUMNS (
        _addindexcolumn,
        "@groupindex",
            SUMX (
                FILTER ( _addindexcolumn, Data[DATE] <= _currentdate ),
                [@consecutivecondition]
            )
    )
VAR _currentgroupindex =
    MAXX ( FILTER ( _addgroupingcolumn, Data[DATE] = _currentdate ), [@groupindex] )
RETURN
    _currentgroupindex

step three Ranking measure: = 
VAR _currentgroupindex = [step two currentgroupindex:]
RETURN
    IF (
        COUNTROWS ( Data ) <> BLANK (),
        RANKX (
            FILTER ( ALLSELECTED ( Data ), [step two currentgroupindex:] = _currentgroupindex ),
            CALCULATE ( MAX ( Data[DATE] ) ),
            ,
            ASC
        )
    )

[vapid128]

RKING=
RANKX (

    Filter(

        'tblW',

        'tbl'[AGE] = EARLIER('tbl'[AGE]) &&

        'tbl'[ID] = EARLIER('tbl'[ID]) &&

        'tbl'[GROUP] = EARLIER('tbl'[GROUP])

    ),

    'tbl'[DATE],

    ,

    ASC

)

[NilR]

@vapid128 Thank you! still doesn't reset the date.

[vapid128 · Accepted solution]

OH OH sorry,

I didnt get what your mean.

rank =

RANKX(

    FILTER(

        'Table',

        [GROUP]=EARLIER('Table'[GROUP]) &&

        [ID]=EARLIER('Table'[ID]) &&

        [AGE]=EARLIER('Table'[AGE])

    ),

    [DATE],

    ,

    ASC

)

Month>1 =

var _NextMonth =

LOOKUPVALUE(

    'Table'[DATE],

    [GROUP],[GROUP],

    [ID],[ID],

    [AGE],[AGE],

    [rank],[rank] +1

)

return (_NextMonth-[DATE])>1

sessionID =

RANKX(

    FILTER(

        'Table',

        [GROUP]=EARLIER('Table'[GROUP]) &&

        [ID]=EARLIER('Table'[ID]) &&

        [AGE]=EARLIER('Table'[AGE]) &&

        [Month>1]

    ),

    [DATE],

    ,

    ASC

)

ID_in_Session =

RANKX(

    FILTER(

        'Table',

        [GROUP]=EARLIER('Table'[GROUP]) &&

        [ID]=EARLIER('Table'[ID]) &&

        [AGE]=EARLIER('Table'[AGE]) &&

        [sessionID]=EARLIER('Table'[sessionID])

    ),

    [DATE],

    ,

    ASC

)