Forum Discussion
Revenue Recognition - Circular Dependencies
Super UserAmortization expansions usually need to be done in Power Query. Keep in mind that Power BI does not have a concept of editable variables, so there is a good chance your requirement cannot be met with Power BI.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi,
Hopefully I did it right this time. I only included the columns that are relevant to the calculation.
The following columns are the ones that have would have calculations and
Rev. Rec. Declining Balance - This column is calculates the monthly Revenue to be recognized. Formula = -(Def. Rev. Balance x (1-Attrition Rates))/( Months Amort-Longevity2+1-Remaining Mths Amort-Longevity)+( Def. Rev. Balance x Attrition Rates))
Accum Rec. Rev. – This column is the cumulative sum of the Rev. Rec. Declining Balance Column as of the current months date
Def. Rev. Balance – This is the column formula = Original Def. - Rev.Accum Rec. Rev. for the current month.
I included what the result should be in the calcualtion columns:
Rev. Rec. Declining Balance
Accum Rec. Rev.
Def. Rev. Balance
| Service | Contract Date | Primary Key | Beginning Date | Def Rec Start Date | Attrition Rates | Remaining Mths Amort-Longevity | Months Amort-Longevity2 | Original Def. Rev. | Rev. Rec. Straight-Line (Declining Balance) | Accum Rec. Rev. | Def. Rev. Balance |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 11/30/2008 | 1.00% | 1 | 5 | 1,044,900.00 | -217,339.20 | -217,339.20 | 1,044,900.00 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 12/31/2008 | 1.00% | 2 | 5 | 1,044,900.00 | -213,096.91 | -430,436.11 | 827,560.80 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 1/31/2009 | 1.00% | 1 | 8 | 1,044,900.00 | -82,184.55 | -512,620.65 | 614,463.89 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 2/28/2009 | 1.00% | 2 | 8 | 1,044,900.00 | -80,602.30 | -593,222.95 | 532,279.35 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 3/31/2009 | 1.00% | 3 | 8 | 1,044,900.00 | -79,043.48 | -672,266.44 | 451,677.05 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 4/30/2009 | 1.00% | 4 | 8 | 1,044,900.00 | -77,507.78 | -749,774.22 | 372,633.56 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 5/31/2009 | 1.00% | 5 | 8 | 1,044,900.00 | -75,994.89 | -825,769.11 | 295,125.78 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 6/30/2009 | 0.50% | 1 | 13 | 1,044,900.00 | -17,867.60 | -843,636.70 | 219,130.89 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 7/31/2009 | 0.50% | 2 | 13 | 1,044,900.00 | -17,694.40 | -861,331.10 | 201,263.30 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 8/31/2009 | 0.50% | 3 | 13 | 1,044,900.00 | -17,522.49 | -878,853.59 | 183,568.90 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 9/30/2009 | 0.50% | 4 | 13 | 1,044,900.00 | -17,351.85 | -896,205.44 | 166,046.41 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 10/31/2009 | 0.50% | 5 | 13 | 1,044,900.00 | -17,182.48 | -913,387.92 | 148,694.56 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 11/30/2009 | 0.50% | 6 | 13 | 1,044,900.00 | -17,014.38 | -930,402.30 | 131,512.08 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 12/31/2009 | 0.50% | 7 | 13 | 1,044,900.00 | -16,847.52 | -947,249.81 | 114,497.70 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 1/31/2010 | 0.50% | 8 | 13 | 1,044,900.00 | -16,681.91 | -963,931.72 | 97,650.19 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 2/28/2010 | 0.50% | 9 | 13 | 1,044,900.00 | -16,517.53 | -980,449.25 | 80,968.28 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 3/31/2010 | 0.50% | 10 | 13 | 1,044,900.00 | -16,354.38 | -996,803.63 | 64,450.75 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 4/30/2010 | 0.75% | 11 | 13 | 1,044,900.00 | -16,272.61 | -1,013,076.23 | 48,096.37 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 5/31/2010 | 0.75% | 12 | 13 | 1,044,900.00 | -16,031.22 | -1,029,107.46 | 31,823.77 |
| Service1 | 10/31/2008 | Service1-10-31-2008 | 11/30/2008 | 6/30/2010 | 0.75% | 13 | 13 | 1,044,900.00 | -15,792.54 | -1,044,900.00 | 15,792.54 |
- lbendlin
In preparation for my attempt I slightly refactored your query for Rev. Rec. Declining Balance but can't get to the same result.
[#" Def. Rev. Balance "] * ([Attrition Rates] + ([Attrition Rates] - 1) / ([#"Months Amort-Longevity2"] + 1 - [#"Remaining Mths Amort-Longevity"]))Can you please validate?