Forum Discussion

Regular Visitor

4 years ago

Solved

Contract renewal, cancellation and renewal amount increase/decrease

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 ...

Need Help

tamerj1

4 years ago

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)
    )

tamerj1

Community Champion

4 years ago

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

Regular Visitor

4 years ago

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
Community Champion
4 years ago
barend_dronkers
Yes you can
- barend_dronkers
  Regular Visitor
  4 years ago
  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!