Forum Discussion

barend_dronkers's avatar
barend_dronkers
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's avatar
    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)
    )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
    barend_dronkers
    Regular Visitor
    4 years ago

    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's avatar
      barend_dronkers
      Regular Visitor
      4 years ago

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