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      

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!

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

7 Replies

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

  • tamerj1's avatar
    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's avatar
      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?