Lookup for specific value

[Anonymous]

Hi, I have two table below and would like to know how can I lookup the value base on certain condition.

Table1:

Date

15/02/2022

21/03/2022

12/04/2022

01/05/2022

11/06/2022

16/07/2022

01/08/2022

Table2:

Date	Value
01/02/2022	1
01/03/2022	2
01/04/2022	3
01/05/2022	4
01/06/2022	5
01/07/2022	6
01/08/2022	7

I am looking for a equation that can return the value from table 2 "Value"

For example when my timeline slicer selected 15/02/2022 - 12/04/2022, it will return the Max value in table 2 for the selected month, which is 3 in this case.

If I select 21/03/2022 - 16/07/2022, it will return 6 as the value.

Whatever period I selected, the equation will return the same month value from table 2.

Thank you.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question and your datamodel correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

Expected result measure: =
VAR _maxdatetable2 =
    MAXX ( FILTER ( Table2, Table2[Date] <= MAX ( Table1[Date] ) ), Table2[Date] )
RETURN
    CALCULATE ( MAX ( Table2[Value] ), Table2[Date] = _maxdatetable2 )