Setting up a billing by month until total is reached

[electrobrit]

I am having a time with this and hoping someone can give me some help.

I've taken a sample of opportunities where the agreement may involve revenue (one-time) and/or revenue (monthly), both we'd start billing 30 days follwing the projected close date.
So if it has a one-time revenue, that gets billed 30 days after close in the full amount and if it has a monthly, the monthly billing begins 30 days after close for the monthly. If both, the one time + monthly, then monthly there after. The # of months for the "recurr amount" which is the total divided by months. (in the first opp you'll it's both a one time and monthly for 1 month).
So it could have first month amount that differs from the monthly amount. Both are billed (and reflected monthly) until the total (Revenue total) is paid.
I've created all of this but then I am stumped at a certain point. (pbix is attached)

What I can't figure out how to do is
1) would like to have it like the speadsheet where it shows the monthly amount by opportunity and a table we these amounts in a table together by month/year so we can see a total billed each month? (the month/year going across)
2) how do i get these amounts to line up and keep billing until they reach the total, then stop? I have several attempts in the pbix file.
I've created start and end dates but can't tell how to join with the date and bring in totals.

Links to my work so far (excel and pbix) below so if someone can assist, would be ever so grateful. I'm stumped!

Test Excel 
Test pbix 

Thank you for your brain power 🙂 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[electrobrit]

@Ashish_Mathur I appreciate your work! The one thing that is confusing is if you see in my pbix, those totals by month is one of the steps I am trying to get to. Totals for one time and recurr are not in the same column. You've set your data up like I can get in excel (monthly per opportunity by doing some manual work) but I can't get in Power BI. Any suggestions? Thanks again.

[Ashish_Mathur]

You are welcome.  I have not done any manual work at all.  I took all your source data and ran transformations on it in the Query Editor.  Please study my calculation carefully.

[electrobrit]

@Ashish_Mathur seriously awesome! I didn't see you had done this on the back-end in query editor but brilliant. Works perfectly. THANK YOU! I was completely stumped for 3 days.

[Ashish_Mathur]

You are welcome.