Return the value for records that is the latest date less than a selected date

[jal1aaa]

I have a data table that lists values of projects as they change over time.  The values are regularly updated and the database keeps all historical updates.  I have been able to create a measure to only select projects with the max create date to only display the latest project and summarize the totals based on the current date.  But I want to be able to select a date in a slicer and have the report return the latest values that are less than or equal to that date.  For example, i have two data tables:

A list of projects and all of their updates

Projects
Project ID	Date Created	Value
Project A	1/1/2024	100
Project A	1/19/2024	125
Project A	2/15/2024	95
Project A	3/1/2024	150
Project B	12/1/2023	65
Project B	1/10/2024	100
Project B	3/15/2024	100

And a date table that has our forecast dates in it

Dates
Forecast	Date
Jan forecast	1/31/2024
Feb Forecast	2/28/2024
Mar Forecast	3/31/2024

Using the data above as an example, I want to create a slicer with the dates and if the user selects the Feb Forecast, the values returned for Project A and B are the ones in red (the latest date prior to 2/28/2024)

Any suggestions?

[ryan_mayu · Accepted solution]

@jal1aaa 

you can create a measure and add that measure to the visual filter

Measure =

VAR _date=maxx(FILTER(all('Projects'),'Projects'[Date Created]<=max(Dates[Date])&&Projects[Project ID]=max(Projects[Project ID])),'Projects'[Date Created])

return if(max('Projects'[Date Created])=_date,1,0)

pls see the attachment below

[v-zhouwen-msft · Accepted solution]

Hi @ryan_mayu ,thanks for the quick reply, I'll share my thought process.

Hi @jal1aaa ,

The Table data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a measure

Measure = 
VAR _a = SELECTEDVALUE('Table2'[Date])
VAR _b = MAXX(FILTER('Table','Table'[Date Created] <= _a),'Table'[Date Created])
VAR _c = LOOKUPVALUE('Table'[Project ID],'Table'[Date Created],_b)
VAR _d = LOOKUPVALUE('Table'[Value],'Table'[Date Created],_b)
RETURN _c & " " & _b & " " & _d

2.Final output

[ryan_mayu · Accepted solution]

@jal1aaa 

you can create a measure and add that measure to the visual filter

Measure =

VAR _date=maxx(FILTER(all('Projects'),'Projects'[Date Created]<=max(Dates[Date])&&Projects[Project ID]=max(Projects[Project ID])),'Projects'[Date Created])

return if(max('Projects'[Date Created])=_date,1,0)

pls see the attachment below