Lookup a value in same table different column

Hi, fabiomanniti 

When the lookupvalue function is used in the measure, the column name can't be used directly in the red line part, but the aggregated value can be used. And you can extend the measure to filter the data for September.

Reference:LOOKUPVALUE function (DAX) - DAX | Microsoft Docs

According to your description, I create a sample. If you want to dynamically select the month, you can create a new date column for filtering. Then create two measures, one for display matching rows, one for count.

Table = CALENDARAUTO()

has_been_renewed 2 = 
CALCULATE (
    LOOKUPVALUE (
        Contracts[Current_ID],
        Contracts[Old_ID], MAX ( Contracts[Current_ID] )
    ),
    Contracts[expiration_date] in DISTINCT('Table'[Date])
)

count_has_been_renewed = 
COUNTX (
    ADDCOLUMNS (
        Contracts,
        "a",
            CALCULATE (
                LOOKUPVALUE (
                    Contracts[Current_ID],
                    Contracts[Old_ID], MAX ( Contracts[Current_ID] )
                ),
                Contracts[expiration_date] IN DISTINCT ( 'Table'[Date] )
            )
    ),
    IF ( NOT ( ISBLANK ( [a] ) ), [a] )
)

PBIX file sample is below.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey