Measure to filter correct date from another table

[nirvana_moksh]

Hello All,

I am running into an issue creating this measure which is driving me nuts. For this example, I have two tables. One has a List of Dates which is used as a filter as below:

which when filtered to one date as above 05.10.2018 looks up other table's start dates and it returns the minimum of that range's start. For example, when 05.10.2018 is selected it should return 05.09.201, but when 05.16.2018 on the filter is selected it returns 05.15.2018 as that is the minimum date close to it.

GROUP ID	VALUE DATE	START DATE	END DATE
1300	3/9/2023	5/9/2018	5/14/2018
1300	5/9/2023	5/15/2018	NULL

LIST DATES (Filter field)

5/1/2018

5/2/2018

5/3/2018

5/4/2018

5/5/2018

5/6/2018

5/7/2018

5/8/2018

5/9/2018

5/10/2018

5/11/2018

5/12/2018

5/13/2018

5/14/2018

5/15/2018

5/16/2018

5/17/2018

I tried and wrote something like IF(SELECTEDVALUE('List Date'[Date] <= MIN(Start Date), MIN(Start Date),"-") but that did not work, any help would be appreciated.

Thank You

[parry2k]

@nirvana_moksh your request is not clear, isn't when 5/16/2018 selected, it should return 5/15/2018 instead of 5/14/2018???

[nirvana_moksh]

@parry2k- Sorry about that, I have edited and corrected my post now

---

---

[parry2k]

@nirvana_moksh try adding following measure

Recent Date = 
VAR __baseDate = SELECTEDVALUE('List Date'[Date])
RETURN
CALCULATE( MAX(Table[Start Date]), Table[Start Date] <= __baseDate )

[nirvana_moksh]

@parry2k - Sadly that did not work, I had tried something similar as well and I think what it ends up doing is checking MAX os the start dates instead of seeing it in relation to the filter selected. Below is what it returned when I used the measure suggested:

[parry2k]

@nirvana_moksh doesn't make sense, share the pbix file thru one drive/google drive, remove any sensitive inforamtion

[nirvana_moksh]

@parry2k - Extracted the small data set from these two tables for your reference, there are actually 10 tables and lot of relationships, but I think this covers it basically for these two. Thanks!!!

https://1drv.ms/f/s!Au9CSosUldp9hE-ZyXFm9ppRHpF1