Contract renewal, cancellation and renewal amount increase/decrease

[barend_dronkers]

I have a Contract table that includes the following columns:

ID	Account	Service	Start Date	End Date	Amount ($)
1	Account A	Service 1	2021/05/18	2022/05/17	$100,000
2	Account A	Service 1	2022/05/18	2023/05/17	$105,000
3	Account B	Service 1	2021/04/13	2022/04/12	$80,000
4	Account B	Service 2	2022/09/15	2022/09/14	$50,000

Consider for this question, today's date is 2022/06/04 (June 4th, 2022). I'd like to create a new table using DAX that summarizes, for each Account and Service, the following:

• Service renewal: the latest contract for each unique Account+Service has an End Date greater than today's date AND a previous contract for the same Account+Service exists whose End Date is within 1 day of the latest contract Start Date.
• New Service sale: the latest contract for each unique Account+Service has an End Date greater than today's date AND no previous contract exists for the same Account+Service whose End Date is within 1 day of the latest contract Start Date. The Service is still considered new if the Account had purchased the same Service in a previous year.
• Service cancellation: the latest contract for each unique Account+Service has an End Date less than today's date.

The new table should look like this (using data from the original Contracts table above):

Account	Service	Action	Most Recent Contract ID	Renewal Rate (%)
Account A	Service 1	Renew	2	105%
Account B	Service 1	Cancel	3	null
Account B	Service 2	New	4	null

In the new table (above) I'd like:

• Action = either New, Renewal or Cancel based on the previously described options
• Renewal Rate = Active contract amount (whose End Date > Today's Date) / Previous adjacent contract amount
• Most Recent Contract ID= contract ID of the most recent instance of the contract matching the Account+Service pair (even if the most recent contract was canceled whose End Date < Today's Date).

I have NO idea where I'd start with this. I have some basic DAX knowledge so hopefully, with some input from the Power BI community, I can get most of the way there!

[Anonymous]

Hello,

I made it with adding 2 columns in the first table.

My table Feuil 1

Code for Action Column  :

Action =

VAR acct = Feuil1[Account]

VAR serv = Feuil1[Service]

VAR _datemaxenddate =

CALCULATE (

MAX ( Feuil1[End Date] ),

ALL ( 'Date'[Date] ),

Feuil1[Account] = acct

&& Feuil1[Service] = serv

)

VAR _nbrows =

COUNTROWS (

FILTER (

SUMMARIZE ( Feuil1, Feuil1[Account], Feuil1[Service], Feuil1[Start Date] ),

Feuil1[Account] = acct

&& Feuil1[Service] = serv

)

)

VAR _cancelstatus =

IF (

_nbrows = 1

&& Feuil1[End Date] < TODAY (),

"Cancel",

IF (

_nbrows = 1

&& Feuil1[End Date] > TODAY ()

&& Feuil1[Start Date] < TODAY (),

"New",

IF (

_nbrows = 2

&& Feuil1[End Date] > TODAY ()

&& Feuil1[Start Date] < TODAY (),

"Renew"

)

)

)

RETURN

_cancelstatus

Code for Renewal

Renewalrate =
VAR lastenddate = Feuil1[End Date]
VAR prevamtcontract =
CALCULATE ( MAX ( Feuil1[Amount] ), Feuil1[End Date] < lastenddate )
VAR result =
DIVIDE ( Feuil1[Amount], prevamtcontract )
RETURN
IF ( Feuil1[Action] = "Renew", result )

code for the final table

feuil1newtable =
FILTER (
SUMMARIZE (
Feuil1,
Feuil1[Account],
Feuil1[Service],
Feuil1[Action],
Feuil1[ID],
Feuil1[Renewalrate]
),
NOT ( ISBLANK ( Feuil1[Action] ) )
)

Hope this helps

[barend_dronkers]

Thank you!! It might actually make more sense to not create an extra table. I'm going to give this a try today. Also appreciate understanding your logic!

[barend_dronkers]

Hi James. I like that your solution uses columns, because it means I can use a slicer visual to filter on the type of action. One problem I'm running into with your solution is that _nbrows counts total number of rows, regardless of whether the previous contracts are actually earlier in time. Action should only flag something as "renew" if the previous contract's EndDate (if any exist) is adjacent (1 day less) than the StartDate of the contract we're flagging as a "renew".

[tamerj1 · Accepted solution]

Hi @barend_dronkers 
Here is a sample file with the solution https://www.dropbox.com/t/MHLqHgqhR8T8rNOc

Action = 
VAR ContractsTable = Contracts
VAR LatestContractEnd = MAXX ( ContractsTable, Contracts[End Date] )
VAR LastContractStart = MAXX ( ContractsTable, Contracts[Start Date] )
VAR PreviousContracts = FILTER ( ContractsTable, Contracts[End Date] < LatestContractEnd )
VAR Last2ndContractEnd = MAXX ( PreviousContracts, Contracts[End Date] )
VAR ContractGap = DATEDIFF ( Last2ndContractEnd, LastContractStart, DAY ) 
VAR Result =
    SWITCH (
        TRUE ( ),
        LatestContractEnd <= TODAY ( ), "Cancel",
        ISBLANK ( Last2ndContractEnd ) || ContractGap > 1, "New",
        "Renew"
    )
RETURN 
    Result

Most Recent Contract ID = MAX ( Contracts[ID] )

Renewal Rate (%) = 
VAR ContractsTable = Contracts
VAR LatestContractEnd = MAXX ( ContractsTable, Contracts[End Date] )
VAR LastContractValue = MAXX ( FILTER ( ContractsTable, Contracts[End Date] = LatestContractEnd ), Contracts[Amount ($)] )
VAR PreviousContracts = FILTER ( ContractsTable, Contracts[End Date] < LatestContractEnd )
VAR PreviousContractEnd = MAXX ( PreviousContracts, Contracts[End Date] )
VAR PreviousContractValue = MAXX ( FILTER ( PreviousContracts, Contracts[End Date] = PreviousContractEnd ), Contracts[Amount ($)] )
RETURN 
    IF ( 
        [Action] = "Renew",
        DIVIDE ( LastContractValue, PreviousContractValue)
    )

[barend_dronkers]

Thanks! In my actual data the Contract ID field is not actually a simple incrementing number. So would I be able to substitute an expression for Most Recent Contract ID that gives me the Contract ID for the contract with the most recent End Date?

[tamerj1]

@barend_dronkers 
Yes you can

[barend_dronkers]

I think I got it working with:

Most Recent Contract ID =

CALCULATE(

MAX(Contract[ID]),

FILTER(Contract, MAX(Contract[End Date]) = Contract[End Date])

)

If there's a more elegant way please let me know! Regardless, I'll mark this as a solution. Your help was very appreciated!