Problem Getting Measure to Give Closest Date Without Exceeding

[rrhutch]

I have a table of data like the following:

APPID	SID	YRTRM	PROGRAM	DEGREE	STATUS	DATE
1	11111	2019 Fall	Undergrad	BS	Pending	1/8/19
1	11111	2019 Fall	Undergrad	BS	Accepted	1/31/19
1	11111	2019 Fall	Undergrad	BS	Deferred	4/15/19
2	22222	2019 Fall	Grad	MDiv	Pending	12/1/18
2	22222	2019 Fall	Grad	MDiv	Accepted	2/10/19
2	22222	2019 Fall	Grad	MDiv	Deferred	3/17/19
3	22222	2020 Spring	Grad	MDiv	Accepted	3/17/19
4	22223	2019 Fall	Grad	MA	Pending	3/1/19
4	22223	2019 Fall	Grad	MA	Pending	3/15/19
4	22223	2019 Fall	Grad	MA	Accepted	5/1/19
5	55567	2019 Fall	Adv	PhD	Pending	2/9/19
5	55567	2019 Fall	Adv	PhD	Pending	3/1/19
5	55567	2019 Fall	Adv	PhD	Accepted	3/31/19
6	11111	2020 January	Undergrad	BS	Accepted	4/15/19

What I a trying to do is indentify a record that is the max record less than the max date selected from a disconnected date slicer. In order to accomplish this, I am using the following measure:

MyDate =
VAR SlicerDate =
    MAX ( dDate[DATE] )
VAR MinDiff =
    MINX (
        FILTER ( ALL ( 'App Data' ), 'App Data'[APPID] IN VALUES ( 'App Data'[APPID] ) ),
        ABS ( SlicerDate - 'App Data'[DATE] )
    )
RETURN
    MINX (
        FILTER (
            ALL ( 'App Data' ),
            'App Data'[APPID] IN VALUES ( 'App Data'[APPID] )
                && ABS ( SlicerDate - 'App Data'[DATE] ) = MinDiff
        ),
        'App Data'[DATE]
    )

The measure is working, but I am not getting a the date return for the record with the date closest, but not exceeding the max date selected. Instead, I am just getting the closet date to the max value in the slicer, even though it might exceed the date.

For example, if I the max date I chose from the slicer is 3/22/19, I get the following result:

Instead, I am wanting it to return:

APPID	SID	YRTRM	PROGRAM	DEGREE	STATUS	DATE	MyDate
1	11111	2019 Fall	Undergrad	BS	Pending	1/8/19	1/31/19
1	11111	2019 Fall	Undergrad	BS	Accepted	1/31/19	1/31/19
1	11111	2019 Fall	Undergrad	BS	Deferred	4/15/19	1/31/19
2	22222	2019 Fall	Grad	MDiv	Pending	12/1/18	3/17/19
2	22222	2019 Fall	Grad	MDiv	Accepted	2/10/19	3/17/19
2	22222	2019 Fall	Grad	MDiv	Deferred	3/17/19	3/17/19
3	22222	2020 Spring	Grad	MDiv	Accepted	3/17/19	3/17/19
4	22223	2019 Fall	Grad	MA	Pending	3/1/19	3/15/19
4	22223	2019 Fall	Grad	MA	Pending	3/15/19	3/15/19
4	22223	2019 Fall	Grad	MA	Accepted	5/1/19	3/15/19
5	55567	2019 Fall	Adv	PhD	Pending	2/9/19	3/1/19
5	55567	2019 Fall	Adv	PhD	Pending	3/1/19	3/1/19
5	55567	2019 Fall	Adv	PhD	Accepted	3/31/19	3/1/19
6	11111	2020 January	Undergrad	BS	Accepted	4/15/19

I know it is something simple in my measure that I am missing, but hoping some other eyes can help me point out what is wrong.

[d_gosbell · Accepted solution]

This could be due to the ABS() in the MinDiff returning the same value if the differences is plus or minus x days.

I would probably simplify this measure down to something like the following

MyDate2 = 
VAR SlicerDate =
    MAX ( dDate[DATE] )
RETURN CALCULATE(max('App Data'[DATE])
  , Filter(all('App Data'[DATE]), 'App Data'[DATE] <= SlicerDate)
)