Weighted average maturity

[ssstaud]

Hello,

I am working with bonds. (People give you money, you have to give it back eventually) 

Basically I am trying to create a graph with weighted time to maturity (the time the company has to give it back) and I can´t wrap my head around that. I am not able to create a measure that is able to do DATEDIFF correctly. 

The calculation logic of weighted time to maturity or weighted average maturity is basically this: 

It can also be interpreted as:

And I am trying to create a graph that looks like this: 

The problem is that I dont know how to create a measure with DATEDIFF, because I need the difference in dates for every month and every bond to be different. (for example: in may the time to maturity for a bond is for example 21 months, in june it is only 20, in july 19 and so on..)

The .pbix file is here:
https://mega.nz/folder/vlo0RCJS#Kxh79nbxIEPh05wX-h1hhw

I would appreciate any help whatsoever!

[OnurTurna]

Hi,

Is there any examples and/or guidance on this question?

[AlejandroVazque]

Ssstaud, I have exactly the same problem, did oyu manage to solve this?

[v-luwang-msft]

Hi @ssstaud ，

The following article is related to weighted average, you can refer to it and hope it can give you ideas.

weighted average

weight average

Weighted Average

Best Regards

Lucien

[ssstaud]

Sadly this does not help as this graph deals with dynamic time. I will try to depict my problem better this time.

What are the important values here:
mature_at: the time when our company has to pay the money back to the client 
amount: how much money we have to pay back (i.e. the weight)

The calculation logic is better described by this:

How it works: Lets say that in may 2016 a person bought a bond that matures in 5 yrs for the amount of $100. So WAM is for may 5 yrs. For June 2016 it is 4 yrs 11 months.
In July sb. bought a bond of the same value of $100 that also matures in 5 yrs. So for July 2016 the WAM is 4 yrs 11 months, because:

And the graph should look like this (eventually ending in a zero, because all the bonds matured i.e. the money was given back to the client) 

Thank you once again for helping me!