Forum Discussion

brief001's avatar
brief001
Helper II
4 years ago
Solved

Lookup value within date range

I'm looking for a dax formula for a calculated column, which searches by name and between two dates. In the picture below the explanation. I'm looking for a DAX formula (calculated column) for the o...
lookup
Search
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi brief001 

    You may try


    VAR CurrentName = Table_1[Name]
    VAR CurrentDate = Table_1[Date]
    VAR FilterTable =
    FILTER (

    Table_2,

    Table_2[Name] = CurrentName

    && Table_2[Date_Start] <= CurrentDate

    && Table_2[Date_End] >= CurrentDate

    )

    VAR Result =
    MAXX ( FilteredTable, Table_2[Team] )

    RETURN

    Result

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi brief001 

You may try


VAR CurrentName = Table_1[Name]
VAR CurrentDate = Table_1[Date]
VAR FilterTable =
FILTER (

Table_2,

Table_2[Name] = CurrentName

&& Table_2[Date_Start] <= CurrentDate

&& Table_2[Date_End] >= CurrentDate

)

VAR Result =
MAXX ( FilteredTable, Table_2[Team] )

RETURN

Result