Forum Discussion
Dynamic Calculation for Expiring Contract Values
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,
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
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,