Lookup date value applied on filter not working

[meowster]

Hello, 

I am trying to use a date value from lookup as a variable for a filter but it doesn't seem to work. My DAX formula is shown as below:

Table =

VAR SelectedSalesDate = LOOKUPVALUE(

    'DIM_SalesDate'[Last day of sales month],

    'DIM_SalesDate'[DateText YYYY-MM],

    SELECTEDVALUE('DIM_SalesDate'[DateText YYYY-MM])

)

RETURN

    FILTER('FACT_Main',

        'FACT_Main'[Last day of sales month] >= SelectedSalesDate &&

        'FACT_Main'[Application Date] <= SelectedSalesDate &&

    )

The above DAX does not return any syntax error and returns a blank table instead. Did this happen because the lookup value is not recognised as a date value? How can I fix this? Thank you!

[johnt75 · Accepted solution]

You can't use SELECTEDVALUE when creating a table. Calculated tables are only calculated during data load or refresh, so they don't pay any attention to slicers or filters. If you need something to react dynamically to filters or slicers you need to do it in a measure.

[meowster]

Thank you @johnt75 for this info. Didn't know about this. I've managed to find a workaround by summarizing the data into another table and then apply the filters from there.