Advanced filters for related tables

[ssstaud]

Hi, 

I have three tables- bonds, transactions and date table.

Bonds:

Transactions (the mature_at column is done using RELATED from bonds table):

I am trying to create a new column in the date table, but whatever DAX expression I write does not work. I am not able to properly use filter between different tables.

The calculation logic is basically this:

Here is my pbix: 

https://mega.nz/folder/vlo0RCJS#Kxh79nbxIEPh05wX-h1hhw

Thank you in advance for any help.

[HotChilli]

"trying to create a new column in the date table" This is not the right approach.

Use the Calendar table to slice and dice the data.

If you want to create a visualisation with a date from the calendar table then create a measure to get the WAM figure. Pull the WAM measure on to the visual.

If you want more help from the forum I suggest you show the desired result .

[ssstaud]

Hello!

I know it is not the right right aproach, but I can´t seem to be able to come up with any other way to solve this. Also, I am not using the calendar table as the main date table, I am using the default one so it should not matter what other columns I have there.  I need to calculate the WAM for every single date, that´s why I am using calendar table (the other columns are there because I just copy pasted it from my previous project)

What this is about. People bought bonds and gave our company money. The company has to give it back some time. WAM stands for weighted average maturity of bonds (so the weighted average for how long till the company has to pay the bonds back). 
(Not important: Here is a summary of what WAM is:
https://www.investopedia.com/terms/w/weightedaveragematurity.asp  ) 

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 I am looking for should look like this (eventually ending in a zero, because all the bonds matured i.e. the money was given back to the client):

Thanks!

[ssstaud · Accepted solution]

I found the solution 🙂 

Sloupec 3 = (SUMX(FILTER(transactions, transactions[start_date].[Date]<'Calendar Table'[Date].[Date]
                                             && transactions[Amount] <> 0
                                             && DATEDIFF('Calendar Table'[Date].[Date],transactions[mature_at].[Date],MONTH) > 0 ),

transactions[Amount]*DATEDIFF('Calendar Table'[Date].[Date],transactions[mature_at].[Date],MONTH)))

/12

/SUMX(FILTER(transactions, transactions[start_date].[Date]<'Calendar Table'[Date].[Date]
                        && transactions[Amount] <> 0
                        && DATEDIFF('Calendar Table'[Date].[Date],transactions[mature_at].[Date],MONTH)
                                                > 0),
transactions[Amount])