Finding Most Recent Date based off Selected Date in Slicer

[PeteSil1020]

Hello,

I am really struggling with such a simple measure that I feel should not be this hard. All I am trying to do is show the most recent date that is less than or equal to the selected date. So for example my slicer has you choose the EndofMonth that you want to see. I would then expect the measure to reflect the below results give the below data. 

EndofMonth = 11/30/2023

Data:

Student 1	11/21/2023
Student 1	10/1/2023
Student 1	4/1/2023
Student 2	10/1/2023
Student 2	11/15/2023
Student 2	12/5/2023
Student 3	8/10/2023
Student 3	5/1/2023
Student 4	9/1/2023
Student 4	8/18/2023
Student 4	7/1/2023
Student 4	10/15/2023

Result:

Student 1	11/21/2023
Student 2	11/15/2023
Student 3	8/10/2023
Student 4	10/15/2023

Any help on this will be greatly appreciated.

[vicky_]

Latest Date = CALCULATE(MAX('Table'[Column2]), 'Table'[Column2] <= DATE(2023, 11, 30))

just replace the hard-coded date with your slicer value

[PeteSil1020]

Unfortunately this isn't working. This is my current measure:

Students by Date =

Var CurrentDate = SELECTEDVALUE('Student Data - SQL'[EndofMonth])

RETURN

CALCULATE(

    MAX('Student Data - SQL'[MaxAsOf]),

    'Student Data - SQL'[MaxAsOf] <= CurrentDate)

When I do this I have the filter for EndofMonth selected to 11/30/2023. When I do this it is limiting the view to only show students that have a date within that month, instead of showing all students.

Result:

Student 1	11/21/2023
Student 2	11/15/2023

[PeteSil1020 · Accepted solution]

I was actually able to solve this myself. The reason it was limiting the dates to that particular month was due to me using the same date column for the filtering. I created a new table such as Calendar = Calendar(Min('Student Data - SQL'[EndofMonth]), Max('Student Data - SQL'[EndofMonth])). I then changed the formula to point toward 'Calendar'[Date] and it worked perfectly.