Forum Discussion
Problem Getting Measure to Give Closest Date Without Exceeding
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.
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) )
1 Reply
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) )