DAX to pick data based on previous latest date with values

[vkboddapati]

Need Help on the below!

I have a factTable like below:

Date	A	B
01-09-2020	1,50,000	2,00,000
06-09-2020	1,30,000	1,80,000
10-09-2020	1,70,000	2,15,000
20-09-2020	1,50,000	2,00,000
26-09-2020	1,22,500	1,33,500
27-09-2020	2,00,000	3,20,000
28-09-2020	2,20,000	4,50,000
02-10-2020	1,50,000	2,00,000
07-10-2020	1,30,000	1,80,000
11-10-2020	1,70,000	2,15,000
21-10-2020	1,50,000	2,00,000
27-10-2020	1,22,500	1,33,500
28-10-2020	2,00,000	3,20,000
29-10-2020	2,20,000	4,50,000

Q: Need a measure to return the values from most recent date of the above table based on my Date selection (Date Table) in the Filter!

example: If I choose any date between: 02-09-2020 to 05-09-2020 in the filter (of Date Table), the measure shall return the values of 01-09-2020 (i.e. 1,50,000 and 2,00,000); similarly if I choose, 27-10-2020 in the filter, I shall get 1,22,500 and 1,33,500 and if I choose, 28-10-2020 in the filter, I shall get 2,00,000 and 3,20,000 against selection.  

Thanks in advance!

[vkboddapati]

@slomkak and @Greg_Deckler Thanks to both of you! I have mix-matched both of your ideas and got the desired result!
Thank you again for your great help!

[slomkak]

Hi,
Please try something like a code below.

LastAvailableRevenue =

VAR LastDateInContext =

MAX ( Dates[Date] )

VAR LastAvailableDate =

CALCULATE ( MAX ( Orders[Order Date] ), Orders[Order Date] <= LastDateInContext, REMOVEFILTERS ( Dates[Date] ) )

VAR Result =

CALCULATE ( [Revenue], Dates[Date] = LastAvailableDate )

RETURN

Result

Best regards,

Kamil

[Greg_Deckler]

@vkboddapati Try using a Complex Selector: The Complex Selector - Microsoft Power BI Community

For example:

Selector = 
    VAR __SelectedDate = MAX('Dates'[Date])
    VAR __CurrentDate = MAX('Table8'[Date])
    VAR __MaxDate = MAXX(FILTER(ALL('Table8'),'Table8'[Date] <= __SelectedDate),[Date])
RETURN
    IF(__CurrentDate = __MaxDate,1,0)