Forum Discussion
Find Next Most Recent Date Dynamically
Hi,
I'm struggling to find a solution in the forum for what I'm trying to accomplish, with that being said thank you and I appreciate any/all help to the resolution to (or guidance to a similar situation that may help resolve) my issue.
I have a table similar to below
| PJB_ID | Created_By | Created_Date |
| 1111 | [email protected] | 1/13/22 |
| 1112 | [email protected] | 2/21/22 |
| 1113 | [email protected] | 3/17/22 |
| 1114 | [email protected] | 2/5/22 |
| 1115 | [email protected] | 1/29/22 |
| 1116 | [email protected] | 4/5/22 |
| 1117 | [email protected] | 2/22/22 |
| 1118 | [email protected] | 3/24/22 |
The first thing I want to do is have a visual (table) that displays the most current PJB_ID the individual created something like this:
| PJB_ID | Created_By | Created_Date (most recent) |
| 1117 | [email protected] | 2/22/22 |
| 1116 | [email protected] | 4/5/22 |
| 1118 | [email protected] | 3/24/22 |
Then if I have a date slicer and someone selects a date that is prior to the (most recent) created date, it will display the next most recent. So the table then would look like this if 2/1/22 was selected from the slicer:
| PJB_ID | Created_By | Created_Date |
| 1115 | [email protected] | 1/29/22 |
| 1111 | [email protected] | 1/13/22 |
| null | [email protected] | null |
I've tried to use the max, last date, and a few other functions but can't seem to get it to work the way I want. I apologize if this type of issue has been resolved already and if it has and someone can shoot me a link to the post, I'd greatly appreciate it.
Thanks for giving this a look and helping.
Regards,
Cody
Hi Cody:
Here is link to file with date table added.
https://drive.google.com/file/d/1pVFYYs3-FHmOVLICwHWj3jvjYtKT1ohM/view?usp=sharing
Measure =var _lastDate=MAXX(FILTER(ALL('Table'),[Created_Date]=MAX('Table'[Created_Date])),[Created_Date])return CALCULATE(MAX('Table'[Created_Date]),FILTER('Table',[created_Date]=_lastDate))You will want to use "Measure. I hope this works for you!
6 Replies
Hi Cody:
Here is link to file with date table added.
https://drive.google.com/file/d/1pVFYYs3-FHmOVLICwHWj3jvjYtKT1ohM/view?usp=sharing
Measure =var _lastDate=MAXX(FILTER(ALL('Table'),[Created_Date]=MAX('Table'[Created_Date])),[Created_Date])return CALCULATE(MAX('Table'[Created_Date]),FILTER('Table',[created_Date]=_lastDate))You will want to use "Measure. I hope this works for you!
Whitewater100,
Thanks for a quick response. So as I look at the file and mess around with it, it appears to do just what I need it to if I utilize the "between" style slicer. Is it possible to do the same thing with a "drop down" or "list" style slicer? I guess what I'm looking for is if it is possible to select a date rather than a range. If not, I can definitely make this one work for what I'm trying to accomplish. Thanks again for the help!
Hello:
Yes you can a drop down or list slicer. Since it's on individual date you may be better off with between slicer, otherwise there is a lot to click. Can you please mark my answer as solution too? Thank you..