Visualizing monthly payments until contract ends

Hi Anonymous 

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