topic Visualizing monthly payments until contract ends in DAX Commands and Tips

Visualizing monthly payments until contract ends

Anonymous — Thu, 21 Sep 2023 08:20:15 GMT

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!

Re: Visualizing monthly payments until contract ends

JoeBarry — Thu, 21 Sep 2023 08:54:13 GMT

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

Re: Visualizing monthly payments until contract ends

Anonymous — Thu, 21 Sep 2023 08:58:59 GMT

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!