Measure w/ 2 Different Date field

[Anonymous]

I have a table that has a column for "Start Date," "End Date" and another for Value. I also have a Date Table that I would like to make a measure off of. 

I would ideally like to display the days of the date table and the values based if the End Date is greater than or equal to the Date Table Date and if the Start Date is less than or equal to the Date Table Date. The data would be as show below

Start Date	End Date	Value
10/2/2017 0:00	12/31/2099 0:00	$1,000.00
10/25/2017 0:00	3/28/2019 0:00	$5,000.00
11/1/2017 0:00	12/31/2099 0:00	($6,500.00)
1/19/2018 0:00	12/31/2099 0:00	$2,100.00
11/30/2017 0:00	1/14/2019 0:00	$3,200.00
12/1/2017 0:00	12/31/2099 0:00	$5,400.00

I linked the date table and the other table together with the Start Date and the Date Table date. Then I created a Measure using the following code: 

Value sum = 
var reporting_date = SELECTEDVALUE(Date_Table[Date])

var Value_Sum=
CALCULATE(SUM(Table[MTM P/L(USD)]), Table[End Date] <= reporting_date, Table[Start Date] >= reporting_date)

return
Calc_MTM

  I do not really get the desired values and instead just get the values summed with the Start Date and the End Date is ignored.

Any ideas on what I am doing wrong? I am assuming this is an issue with relationships?

Thanks in advance!

[mwegener · Accepted solution]

Hi @Anonymous ,

check this out.

PBIX

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[Anonymous]

Thanks Marcus!

I had to remove the relationship I created, create this measure against the Data Table and use a filter expression embedded within the calculate function.