creating a filtered calculated table

[zoubeyrB]

Hello everyone,

In my report I have two tables : Date and Sales (not connected to each others).

I added a slicer based on Date[date] and I'm trying to create a calculated table showing only sales with dates former to the date selected from Date.

The following formula works :

FilteredTable = 
var summarized_ = SUMMARIZE(Sales,Sales[ID_Sale],Sales[ID_Client],
"FINAL_SALE_DATE",MAX(Sales[Sales_Date]))
var date_ = DATEVALUE("2024-01-01")

return filter(summarized_,[FINAL_SALE_DATE] <= date_)

But when I use the selected date I get an empty table :

FilteredTable = 
var summarized_ = SUMMARIZE(Sales,Sales[ID_Sale],Sales[ID_Client],
"FINAL_SALE_DATE",MAX(Sales[Sales_Date]))
var date_ = DATEVALUE(LASTDATE('DATE'[Date]))

return filter(summarized_,[FINAL_SALE_DATE] <= date_)

I'd appreciate your help on this issue 🙂

[zoubeyrB]

Thanks @johnt75 , I added a measure and filtered on it and it worked.

[johnt75 · Accepted solution]

Calculated tables are only calculated during data refresh, they don't pay any attention to filters or slicers. If you want something to react dynamically you have to use a measure.