Visualizing monthly payments until contract ends

[Powerbiuser1234]

Hi, 

i have the following situation:

I'd like to create a bar graph, which shows me the montly payments i receive from different accounts, until their contracts end. In my table i have the information of the montly fee, the entire amount left to pay and the end date. If i'd create a table with the expiring date on the x axis and the monthly fee on the y-axis, obviously the graph will only show me, which monthly fee expires when.

But i'd like the monthly fee to show up in graph every month, until the contract is expires. Hope it's explained well enough.

Can anyone help me out with this? Thanks in advance!

[Powerbiuser1234]

Hi @JoeBarry, thanks for the quick response. I think your way will also work. Seconds ago i just found a solution within the community, where the problem ist solved within one Dax Code line. See below:

https://community.fabric.microsoft.com/t5/Desktop/Future-Monthly-Recurring-billing/m-p/3387353

But thanks, appreciate it!

[JoeBarry · Accepted solution]

Hi @Powerbiuser1234 

I had a similar use case, the way I done it will enlarge your dataset, but you will get the result you want. 

Do you have a Payment Start Date? If not you will need to define this. 

• Open Power Query and go to your table
• Create a custom column called Months and Divide the Total Amount by the Monthly amount. This will tell you how many months the payments run for. Format as Whole number
• Then create another column called start date and add this code 

Date.AddMonths(Date.From([EndDate]), -[Months])​

• Then we will create a row for each monthly payment. Add a custom column, call it Intervals 

List.Numbers(1,[Month])​

• Expand the column and you will see a row for each month of the payment duration
• We will now create another custom column to create a Payment date 

Date.EndOfMonth(
  Date.AddMonths(
    [EndDate],
    0 - [Months] + [Intervals]
  )
)​

• Convert to date

Load the data into your report. If you are using a date table, create a one to many relationship on the date from the date table and the payent date in your table. Use the the columns from your date table in visuals or slicers. If you aren't using a date table, use the Payment date in your visuals or slicer. Ad the monthly fee to any visual to get the result you need.

Thanks

Joe

If this post helps, then please Accept it as the solution