Sum all values per row until limit is reached with multiple criteria

Hi All,

This problem really is a beast and has me stuck so let me give some background first...

- Data relates to SAAS sales contracts that can be for any number of months but that are usually <= 36 months

- I am looking at price, start date, end date, length of contracts' term in months, product sku, product category customer account ID, etc. 

- I also have a lot of historical data from contracts that are expired or terminated due to addendums

I am trying to start at the latest end date of a contract and calculate the last 12 months of revenue under that customer ID and under that specific product category. 

Here is my most basic example first before trying a more complex scenario: (link to Power BI Table screenshot below)

https://drive.google.com/open?id=1mQ1De2HcZjpqH8GNepBJpL6AWZMm57gX

In this example and while being filtered on this account id and product category, I need to sum the last 12 months of price. This starts by adding 49,667 + 193,151 = 242,818 but this is only 10.19 months and I need the last 12 months so I then need to go down to the next 3 rows from the previous contract and essentially take the monthly price (by dividing Price by Initial Term) and then multiplying it by (12 months minus 10.19 months) which would result in a total 12-month value since 6/29/2020 of $309,973. 

This example gets me to 12 months just by going through 2 previous contracts but it's possible to need to go back further to get to the past 12 months of revenue (ex: 10.19-month contract 1, 1-month contract 2, 15-month contract 3. This would require adding up a portion of revenue from all 3 contracts.)

How can I do this in Power BI to sum up the price by row until Term reaches 12 months? 

Please let me know if you need more information to understand the issue as I know it can be very confusing. Also, if this problem can be solved then I have additional scenarios that are much more complex that I'd love to get help on as well.

Thanks so much!

-Adam

Anonymous