How do i filter using a max variable within a variable chain while creating a table

[user35131]

i have a formula that looks like this. This works and gives me date above 01,01,2022

Final Table = 

Var Table1=

"Pulled a table out of here

Var Table2 =

ADDCOLUMNS(Table1,"rank_order", RANKX(FILTER(Table1,[id]=EARLIER([id])),[date],,ASC,Dense))

var Table3 = 
FILTER(Table2,[date] >= DATE(2022,01,01))

return Table3

However I want to do something like this becasue I want to create a dynamic table outcome.

Var Table1=

"Pulled a table out of here

Var Table2 =

ADDCOLUMNS(Table1,"rank_order", RANKX(FILTER(Table1,[id]=EARLIER([id])),[date],,ASC,Dense))

var Table3 = 
FILTER(Table2,[date] >= DATE(MAX(Table2[date]),01,01))

return Table3

note before I had Date(2022,01,01), but I want Date(Max(table2[date]),01,01). However when I try to do it this way I get "Cannot identify the table that contains [date] column.

[amitchandak]

@user35131 , try table 3 like 

var Table3 =
FILTER(Table2,[date] >= DATE(year(MAX(Table2[date])),01,01))

return Table3

[user35131]

I got the same response that said cannot identify the table that contains [date] column.

[v-rzhou-msft · Accepted solution]

Hi @user35131 ,

I checked your code and I think Table2 should be a virtual table in your calculated table code. We couldn't use MAX to determind the column [Date] in virtual table directly. So you will get this error. You can try MAXX function to do this.

Table =
VAR _Table1 = 
"Pulled a table out of here"
VAR _Table2 =
    ADDCOLUMNS (
        _Table1,
        "rank_order", RANKX ( FILTER ( _Table1, [id] = EARLIER ( [id] ) ), [date],, ASC, DENSE )
    )
VAR _Table3 =
    FILTER ( _Table2, [date] >= DATE ( YEAR ( MAXX ( _Table2, [Date] ) ), 01, 01 ) )
RETURN
    _Table3

My Sample:

Result is as below.

 

Best Regards,
Rico Zhou

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