Dynamic Calculation for Expiring Contract Values

[jeffshields]

Hi! Thank you in advance for any tips, support, etc...

Here's the issue: I have a contract data table where the revenue (v_rpt_agreementlist[monthly billing amount]) of the contract is summarized as below:

The date is pulled from the Date table hierarchy which is linked to the End Date of the contract. So in essence, the above table shows the total amount of revenue that will expire in a given year/month. The blank row is the total value of all contracts with no End Date - those are considered to go on through 2028.

The client would like to see a dynamic, decreasing total for revenue - something akin to the below mockup:

Such that, whatever date selection is made, the table shows the sum total of revenue per month for all contracts in that date selection as Total Revenue:  in this case 340,266.

Then, as the contracts expire, the revenue for that month is deducted from the total and the remaining revenue is calculated. Looking at Nov of 2024 in the above example, the Total Revenue column starts at 304,608 due to Oct remaining revenue. It ends at 304,454 after the Nov contracts expire (154).

Is this an easy dynamic calculation to make? Would it be feasible to make it a Visual Calculation? If you need any additional information, please let me know.

Thank you!

[DataNinja777]

Hi @jeffshields ,

Regarding this question, is your revenue recognized on a straight-line basis over the contract period? Additionally, you mentioned that billing (invoicing) is done monthly, meaning there are no balance sheet entries for deferred revenue (when cash is received upfront) or accrued revenue (when payment is made after the service is performed at the end of the contract).

There are several ways to calculate revenue recognition dynamically and flexibly over the contract period using DAX. To do so, you will need the following information:

• Contract ID
• Contract amount
• Start date
• End date
• Payment dates and amounts

From a financial standpoint, if billing occurs monthly, there is no misalignment between revenue recognition and cash flow. This contrasts with situations where revenue is deferred due to upfront payments or accrued when payments are received in arrears at the end of the contract.  Please let me know if you have the information above, and I will provide more relevant details regarding dax formula. 

Best regards,

[jeffshields]

Hi @DataNinja777 - thanks for the reply! In this case, monthly billing is amount a client pays each month on a separate invoice. Each contract is considered monthly for several years and is recognized in a straight line basis as services are performed (primarily) on a monthly basis.

Regarding the information required for DAX:

• Contract ID (v_rpt_agreementlist[agr_header_recid])
• Contract amount(v_rpt_agreementlist[monthly_billing_amount])
• Start date(v_rpt_agreementlist[datestart]
• End date(v_rpt_agreementlist[dateend]
• Payment dates and amounts - assumed that each month the monthly payment is made in full. 3 year agreement for $100/month. 36 payments of $100 = 3,600 total revenue. 

I'm still working on it and have been beating my head against the wall. It's almost like a reverse running sum/total, where you start with the sum of all revenue and then subtract the expiring contract(s) value each month until you're only left with those contracts that have no end date. The concept is simple but the execution has escaped me.

Appreciate your help - would it beneficial to see the model? It's just the Date table and Agreement List table, joined on End Date but let me know regardless if you need anything else.

edit: just as a heads up, we managed to create this - which is a hardcoded version of the function I'm looking to achieve. the calc below pulls in all contracts, not just those selected within the dates selected, so it's useless outside of an unfiltered table. It's lacking that dynamic quality

Monthly Amt Run Total by DateEnd = 
VAR TotalBillingAmount = 
    CALCULATE(
        [NN_Total_Billing_Amount_Monthly],
        REMOVEFILTERS(v_rpt_AgreementList)
    )

VAR CurrentDateEnd = 
    MAX(v_rpt_AgreementList[DateEnd]) -- Gets the current row's DateEnd

VAR PreviousRows =  
    FILTER(
        ALL(v_rpt_AgreementList),
        v_rpt_AgreementList[DateEnd] <= CurrentDateEnd && -- Compare based on DateEnd
        v_rpt_AgreementList[Agreement_Status] = "Active" &&
        NOT(ISBLANK(v_rpt_AgreementList[DateEnd])) -- Ensure the End Date exists
    )

VAR TotalBillingAmountBeforeCurrent = 
    SUMX(PreviousRows, v_rpt_AgreementList[Monthly Billing Amount]) -- Sum over previous filtered rows

RETURN
   TotalBillingAmount - TotalBillingAmountBeforeCurrent

[DataNinja777 · Accepted solution]

Hi @jeffshields ,

Thank you for the detailed explanation. In fact, there are multiple ways to approach the topic of revenue recognition. Instead of using a fixed monthly allocation, I will demonstrate a method that allows daily proration of revenue, which is particularly useful for contracts that do not start on the 1st of the month.

For this example, I have generated dummy data using your provided table and field names. Additionally, to facilitate daily revenue recognition across the contract period, I have added a calculated column to show the total contract value.

Your data model will look like the one below, where the fact table 'v_rpt_agreementlist' has no relationship with the calendar table (a disconnected table).

First, you will calculate the cumulative revenue recognized throughout the contract period as follows:

Revenue recognition (Daily) = SUMX (
    v_rpt_agreementlist,
    IF (        v_rpt_agreementlist[datestart] <= MAX ( 'Calendar'[Date] )
            && v_rpt_agreementlist[dateend]>= min ( 'Calendar'[Date] ),
        v_rpt_agreementlist[Daily contract revenue]
            *( if(max( 'Calendar'[Date])<v_rpt_agreementlist[dateend],max( 'Calendar'[Date]),v_rpt_agreementlist[dateend])- v_rpt_agreementlist[datestart] ),
        BLANK ()
    )
)

The visualization of the above dax will look like below:

Next, you will calculate the total contract value for the entire contract period.

Total contract value = SUMX (
    v_rpt_agreementlist,
    IF (        v_rpt_agreementlist[datestart] <= MAX ( 'Calendar'[Date] )
            && v_rpt_agreementlist[dateend]>= min ( 'Calendar'[Date] ),
        v_rpt_agreementlist[total contract],
        BLANK ()
    )
)

The visualization of the DAX formula above is shown below:

As the final step, you will subtract the recognized revenue from the total contract value to obtain the required output.

Remaining contract = [Total contract value]-[Revenue recognition (Daily)]

This will resemble an inverted triangle, as shown below:

I have attached an example pbix file for your reference.

Best regards,