I need a DAX formula to calculate number of consecutive year

I am trying to get code DAX formula to get number of consective year a client is with us. There may be multiple entry in a year for the same client (Register number). Then count shall reset if there is a gap between the years. Sample data and expected outcome is attached

I have modified the formula from this resource: https://community.fabric.microsoft.com/t5/Desktop/Calculating-Consecutive-Years-Active/td-p/1670339 but the Formula result is not what i expected:

Consecutive Yrs = 
VAR vMaxYear =
    MAX ( 'Data: Policy'[Effective year] )
VAR vThisCustomer =
    MIN ( 'Data: Policy'[Transformed Register number])
VAR vLatestNotDonation =
    MAXX (
        FILTER (
            ALL ( 'Data: Policy'[Effective year]),
            ISBLANK (
                CALCULATE (
                    COUNTROWS ( 'Data: Policy' ),
                    'Data: Policy'[Transformed Register number] = vThisCustomer
                )
            )
        ),
        'Data: Policy'[Effective year]
    )
VAR vLatestIfBlank =
    IF (
        ISBLANK ( vLatestNotDonation ),
        MINX (
            ALL ( 'Data: Policy'[Effective year] ),
            'Data: Policy'[Effective year]
        ) - 1,
        vLatestNotDonation
    )
RETURN
    IF (
        vMaxYear = 2023,
        vMaxYear - vLatestIfBlank
    )

Effective year	Register number	Formula Outcome	Expected ConsecutiveYears
2017	AX1234	0	1
2018	AX1234	0	2
2020	AX1234	0	1
2020	AX1234	0	1
2021	AX1234	0	2
2022	AX1234	1	3
2023	AX1234	1	4
2023	AX1234	1	4
2023	AX1234	1	4
2023	AX1234	1	4
2023	AX1234	1	4

Appreciate your assistance. Many thanks

Solution

I think I found a better way without applying so many transformations and that's why I have labeled my earlier solution as Solution-2 and labeled the new solution as Solution-1.

Solution-1

First create a Measure as below-

check_break_measure = 

var cr_year = min(your_table_name[Effective year])
var cr_reg_number = MIN(your_table_name[Transformed Register number])


var prev_year = CALCULATE(
    max(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] < cr_year
            && your_table_name[Transformed Register number] = cr_reg_number
    )
)

return if(ISBLANK(prev_year) || cr_year - prev_year >= 2, 1,0)

And then the second Measure as below-

serial_no = 

var current_row_year = min(your_table_name[Effective year])
var current_row_reg_number = min(your_table_name[Transformed Register number])

var break_year_current_group = 
CALCULATE(
    max(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[check_break_measure] = 1
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

var sl = 
CALCULATE(
    DISTINCTCOUNT(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[Effective year] >= break_year_current_group
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

Return sl

Both above and below solution you will get the same output

Solution-2

If you can do some transformation in your table in the power query editor and then create a Measure, you can achieve the requirement.

First, apply these below code to your table's Advance Editor-

let
    //Existing Steps,
    

    //new steps started from here
    //please edit "your_previous_step_name" properly in the below line with your actual your_previous_step_name

    #"Sorted Rows1" = Table.Sort(#"your_previous_step_name",{{"Transformed Register number", Order.Ascending}, {"Effective year", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows1", "Index", 1, 1, Int64.Type),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Effective year"}),
    #"Added Custom" = Table.AddColumn(#"Reordered Columns", "Index_", each [Index]+1),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom",{"Index", "Index_", "Transformed Register number", "Effective year"}),
    #"Merged Queries" = Table.NestedJoin(#"Reordered Columns1", {"Index"}, #"Reordered Columns1", {"Index_"}, "Reordered Columns1", JoinKind.LeftOuter),
    #"Expanded Reordered Columns1" = Table.ExpandTableColumn(#"Merged Queries", "Reordered Columns1", {"Transformed Register number", "Effective year"}, {"Reordered Columns1.Cat", "Reordered Columns1.Year"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Reordered Columns1",{{"Reordered Columns1.Cat", "pr_cat"}, {"Reordered Columns1.Year", "pr_year"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Index", Order.Ascending}}),
    #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "check_break", each if [Transformed Register number] <> [pr_cat] or [Effective year] - [pr_year] >=2 then 1 else 0),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index_", "pr_cat", "pr_year"})
in
    #"Removed Columns"

Considering your sample data, you will have data as below now-

Now go to the Report designer and create a new Measure with this below code-

serial_no = 

var current_row_year = min(your_table_name[Effective year])
var current_row_reg_number = min(your_table_name[Transformed Register number])

var break_year_current_group = 
CALCULATE(
    max(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[check_break] = "1"
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

var sl = 
CALCULATE(
    DISTINCTCOUNT(your_table_name[Effective year]),
    FILTER(
        all(your_table_name),
        your_table_name[Effective year] <= current_row_year
            && your_table_name[Effective year] >= break_year_current_group
            && your_table_name[Transformed Register number] == current_row_reg_number
    )
)

Return sl

Your final data output will be as below-