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 orange column in table_1.

 

I have found many similar questions with solutions on the internet. However, those solutions just don't work. I hope someone can help me with the final solution through this community site.

 

Thanks in advance for the help!
Bas.

 

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

2 Replies

  • brief001's avatar
    brief001
    Helper II
    4 years ago

    Thank you very much, this formula does indeed work. One note though, I had to change "MAXX ( FilteredTable, Table_2[Team] )" to "MAXX ( FilterTable, Table_2[Team] )".

    Thank you, I am very happy to have been helped so quickly!

  • 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