Find Next Most Recent Date Dynamically

[codestule]

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	john_1@apple.com	1/13/22
1112	john_1@apple.com	2/21/22
1113	john_1@apple.com	3/17/22
1114	mary_w@apple.com	2/5/22
1115	bill_9@apple.com	1/29/22
1116	john_1@apple.com	4/5/22
1117	bill_9@apple.com	2/22/22
1118	mary_w@apple.com	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	bill_9@apple.com	2/22/22
1116	john_1@apple.com	4/5/22
1118	mary_w@apple.com	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	bill_9@apple.com	1/29/22
1111	john_1@apple.com	1/13/22
null	mary_w@apple.com	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

[Whitewater100 · Accepted solution]

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]

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..

[codestule]

Hello,

I'm not sure if I'm missing something or what I may be doing wrong but as soon as I change the slicer from a "between" style to a "list" or "drop down" the calculated measure doesn't seem to display when I select a date.  Any thoughts on what I might be missing/doing wrong?

[Whitewater100]

Hi:

If you do the other styles of slicers and pick a date with no data, no measure can appear. For what I've seen so far, the BETWEEN slicer will work best. The formula is based on date, so using date field is really important and since we are asking for DAX to review all dates to pick the last one, between slicer makes sense. I hope this helps..

[codestule]

Hello,

I follow what you say, and that makes a lot of sense.  Thanks again for all of the input/guidance.

Regards,

Cody

[codestule]

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!