Lease amortization schedule data model

[DataNinja777]

Hi Power BI Community,

I am trying to prepare Lease amortization schedule using excel Power Pivot data model.  

My data model uses disconnected table like below where the calendar table and the lease table are disconnected tables.  I used the calendar table as a disconnected table because there are two date fields in the lease table, namely lease start date and lease end date, and if I create a relationship between the two tables, two date fields cannot be filtered properly.  

I am trying to calculate the cumulative lease payments over the lease periods which incrementary accumulates, but I cannot use standard cumulative dax formula because there is no relationship between the lease table and the calendar table.  Could anyone let me know how I can tweak the formula below so that the month number of 1 accumulates over the lease period even though there is no physical relationship between the lease table and the calendar table?  Whe 

I'd like the out9put below to cumulate over the lease period like 1, 2, 3, 4, instead of all showing 1. 

Thank you for your help.

[Ashish_Mathur]

Hi,

I cannot understand your requirement.  Share data in a format that can be pasted in an MS Excel file and show the expected result.

[DataNinja777]

Hi @Ashish_Mathur 

Thank you for your response.  Lease amortization schedule is a standard accounting topic, which is normally calculated using the excel template like attached in the link below, or using a specialized software build for this purpose.  

Basically, 4 pieces of information is required in the lease master data:

• Lease identification number and name
• Monthly lease payments
• Lease start dates
• Lease end dates

The difference between lease start date and lease end date is the lease period during which monthly lease payments are paid. What is usually done is that lease payments are paid monthly over the lease period and lease assets and liabilities are recorded at the beginning of the lease as an assets and liabilities and amortized over the lease term. 

Instead of using the excel, I was trying to prepare the lease amortization schedule using the disconnected table and the lease table which has two dates fields (lease start date and lease end date).  I found it difficult to accumulate the lease period over the lease term using usual cumulative over time dax function because there are no relationship between lease table and the calendar table.  

https://www.occupier.com/lease-amortization-schedule/

There's another complication of discount rates applied to lease amortization schedule, but I think I will be able to tweak the formula is I can find how to express the cumulative monthly payments over time when there are no relationship between the calendar table and lease table.  

Thank you for your help.