how to get values other records in a table based on the current row contents

[MikeBro]

Newbie here on DAX

I have a table of contracts containing ID, contract name, customer ID, start date, end date and type, value. The table holds the list of contracts the customer has with us but also conains entries for contracts to be made live when they renew. So we would have something like:

ID	name	cust id	start	end	type	value
111	support contract 2023	AA999	1/1/23	31/12/23	service 1	1000
567	support contract 2024	AA999	1/1/24	31/12/24	service 1	1200

I want to be able to show in a table visual the old versus the new values (and dates etc)

I want something that finds the 'next years' contract data (if one exists) by matching the customer ID, type and the (end date of current + 1 day) == start date of next year. In reality there are other parameters in the table that I can also match on to make sure I am picking the correct pair of contracts as there can be multiple current and future contracts per customer.

I tried a few things but can't get my head around it. I need a pointer to start and I will work out the details.

[MikeBro]

Thanks, that worked. I was thinking about duplicating the table (which didn't seem sensible) or doing something with virtual tables (which I couldn't get my hea around).

[rbriga · Accepted solution]

Perhaps a simple solution would not be in DAX at all.

You can create a second fact table for future contracts with a start and end date to connect them.

As for a DAX solution, this worked for my testing grounds:

Next Contract Value = 
SUMX(Contracts,
    VAR _NextContractStart= Contracts[Contract End]+1
    RETURN
    CALCULATE(SUM(Contracts[Value]),
    ALLEXCEPT(Contracts,Contracts[Customer]),
    KEEPFILTERS(Contracts[Contract Start]=_NextContractStart)
    )
)

Victory!