Forum Discussion
Measure w/ 2 Different Date field
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!
