Forum Discussion
Impactful IndividualMeasure to filter correct date from another table
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
6 Replies
- parry2k
Super User
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
nirvana_moksh try adding following measure
Recent Date = VAR __baseDate = SELECTEDVALUE('List Date'[Date]) RETURN CALCULATE( MAX(Table[Start Date]), Table[Start Date] <= __baseDate )
