Need to create a measure to find the most recent Activated Date in a table based on a Slicer

[cmills_Greystar]

I need to create a measure that will return the most recent activated date based on a slicer that uses the Month End field.  

I have a Date table that contains daily dates as well as the corresponding month end date.  I have a second table of accounts that contains the date the account was activated and the balance listed. Here is an excerpt from that table:

Account #	Activated On	Balance Listed
48420042	10/26/2023	2227.58
58628732	10/23/2023	208.42
96084543	10/21/2023	518.97
20579235	9/15/2023	500.1
21981256	9/14/2023	12894.01
64400244	9/12/2023	1477.34
68254124	8/31/2023	23521.16
35257921	8/22/2023	7664.71
17481560	8/17/2023	6466.5
34625894	8/10/2023	806.8
68058464	8/3/2023	676.86
44665189	7/18/2023	277.33
63283950	7/18/2023	468.71
77964710	7/10/2023	315.66
47951207	5/24/2023	5164.04
21195665	5/18/2023	189.8
56581764	4/21/2023	724.22
42340253	4/21/2023	289.58
53456282	4/21/2023	78.2
48097770	4/10/2023	150.34
82539865	4/10/2023	148.5
28575229	4/3/2023	1080.63
89074508	3/10/2023	534.16
67578125	3/1/2023	1150.48
86602798	2/28/2023	65.2
46717860	1/31/2023	3821.52
17626258	1/12/2023	496.28
96163617	1/4/2023	399.53
49621585	1/4/2023	10357.32

So, for example: 

Slicer Selection = 9/30/2023, Most Recent Listed = 9/15/2023  (omitting all three dates from October)

Slicer Selection = 6/30/2023, Most Recent Listed = 5/24/2023 (No listings in the month of June, so it goes back to the most recent file prior to that)

Slicer Selection = No Date Selected, Most Recent Listed = 10/26/2023

This is the measure I have right now: 

Last Listed Date =

var SelectedDate = MAX(Date_Lookup_Table[MonthEnding])

RETURN

    CALCULATE(

        MAX(Files_Activation_Info[Activated On]),

        FILTER(

            Files_Activation_Info,

            Files_Activation_Info[Activated On] <= SelectedDate)

        )

But it is only returning the dates from WITHIN the month selected. So if I select 6/30/2023, it will return blank.

[Anonymous]

Hi @cmills_Greystar ,

Use the below measure

Measure = 
 var mxd= MAX(Calender[Date])
 var maxdt=CALCULATE(MAX(Date_Lookup_Table[Activated On]),FILTER(all(Date_Lookup_Table),Date_Lookup_Table[Activated On]<=mxd))
 Return
 maxdt

[cmills_Greystar]

I've already attempted that. a version of that formula is in my original post at the bottom. 

But it is only returning the dates from WITHIN the month selected. So if I select 6/30/2023, it will return blank.

[Anonymous]

In my case it is returning 24-05-2023

[Anonymous]

Can you share the expected output from the above table? It will help to understand and provide a solution 

[cmills_Greystar]

I have included my expected result in my post above in the section where it says "So, for example" 

[lbendlin]

What should happen when you select 4/30/2023 in your slicer?

[cmills_Greystar]

It should return 4/21/2023 as a singular value. While there are three accounts listed on that day, All I'm looking for is that is the latest date on/prior to the selected date in my slicer. 

[lbendlin]

Your tables need to be disconnected.

[cmills_Greystar]

What does that mean?  Remove the Relationship?

[lbendlin]

either remove it or make it inactive (so you can use it with USERELATIONSHIP in a measure when needed)

[cmills_Greystar]

That does not solve the issue.  Now the slicer doesn't change what is returned at all.