Dynamic calculatable table

Hi BBConsultancy ,

You can use the dates of a date table as a slicer, please note that the table cannot have a model relationship with other tables.

(1) This is my test data.  

Reserveringen Table:

Date table:

Datum = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

(2) We can create a calculated table, and you can see that when we use dynamic parameters in the calculated table, the calculated table outputs empty, because the calculated table is static.

For example, if the date we want to select for the slicer is 2024/3/1, we write a fixed parameter to the calculation table to show the table data if the slicer selects 2024/3/1.

Due to known limitations, the calculated table is static, but we can place it as a variable in a measure, and we can create a variable storage dynamic virtual table. For example, let's get the VisitorCount value based on the date selected by the slicer. Then we can write DAX like this

VisitCountFrequency = 
var _VisitCountSummary =
SUMMARIZE(
    Reserveringen,
    Reserveringen[Email],
    Reserveringen[Reservation date],
    "VisitorCount", [Aantal geaccepteerde reserveringen]
)

var _table2 =
FILTER(
    _VisitCountSummary, [Reservation date]=SELECTEDVALUE(Datum[Date])
)
RETURN MAXX(_table2,[VisitorCount])

You can see that the result is 2. At this time, we can look at the table Table2 again, and the result of VisitorCount is also 2.

In the same way, we can create a calculation table with a fixed filter parameter of 2024/3/3. Then select 2024/3/3 in the slicer and see if the results of the measure match the results in the calculation table to confirm that the DAX function is correct.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.