Filter a column by the current row value of that column

[agudis]

I have a column of dates (not continuous)

File Date

4/10/2023

4/3/2023

4/6/2023

4/1/2023

4/25/2023

3/21/2023

I want to make a calculated column that returns the latest date in the column that is before than the current file date.

File Date	Previous Date
4/10/2023	4/6/2023
4/3/2023	4/1/2023
4/6/2023	4/3/2023
4/1/2023	3/21/2023
4/25/2023	4/10/2023

3/21/2023

BLANK

However I can't figure out how to filter a column by value in it's own row without creating a circular dependency. In my code below, is there a way to make 'Table1'[File Date] refer to the [File Date] column on the left, and the current row value on the right?

Previous Date = CALCULATE(

        MAX('Table1'[File Date]),

        'Table1'[File Date] < 'Table1'[File Date]

    )

[FreemanZ · Accepted solution]

hi @agudis 

try like:

Previous Date =

VAR CurrentDate = Table1[Date]

RETURN

MAXX(

    FILTER(

        Table1,

        Table1[Date]<CurrentDate

    ),

    Table1[Date]

)

[agudis]

Works great, thank you!