Best way to use a Date value from one table to look up another table's Date range value?

[Anonymous]

Hi,

I have TableA that contains multiple columns. One of these columns is a Date column (i.e. 11/02/2017).

I would like to use this single Data column's value to look up against, the below, TableB and select the column A value.

For example,

The Date chosen from TableA is '11/02/2017'. I would like to look up the 'MPB Meeting Date' in TableB and it's active date range, and then select the TableB's columnn A value.

So for the TableA date of 11/02/2017 the return value is 'January 2017 MPB'.

What is the best way to do this?  Is there a DAX way, or would I have have to populate a Date table column to hold every date through the year with an additional column stating 'January 2017 MPB' so it can look up against?

With the latter, TableB may often change and therefore the date ranges will need updating often.

I have rushed this question (apologies) but I hope it makes sense on what I'm trying to achieve.

Thanks.

[v-ljerr-msft · Accepted solution]

Hi @Anonymous,

If I understand you correctly, you should be able to use the formula below to create a measure to get the expected result in your scenario. 

measure =
VAR selectedDateFromTableA =
    MAX ( TableA[Date] )
RETURN
    CALCULATE (
        FIRSTNONBLANK ( TableB[ColumnA], 1 ),
        FILTER (
            TableB,
            TableB[Start date period] <= selectedDateFromTableA
                && TableB[MPB Meeting Date] >= selectedDateFromTableA
        )
    )

Regards