Get value from other row in table

[Carla17]

Hi, 

I'm looking for a formula display the column "previous close" in this table.

My table is linked to a proper functioning date table. A formula with "PREVIOUS" is not working because of weekends. 

Thnx for helping me out.

Carla

[v-jingzhang · Accepted solution]

Hi @Carla17 

You can use the following two methods to add a new column.

Previous Close =
MAXX (
    TOPN (
        1,
        FILTER (
            'Table',
            'Table'[Ticker] = EARLIER ( 'Table'[Ticker] )
                && 'Table'[Date] < EARLIER ( 'Table'[Date] )
        ),
        'Table'[Date], DESC
    ),
    'Table'[Close]
)

Or 

Previous Close 2 = 
VAR _previousDate =
    MAXX (
        FILTER (
            ALL ( 'Date' ),
            'Date'[Work Day] = 1
                && 'Date'[Date] < EARLIER ( 'Table'[Date] )
        ),
        'Date'[Date]
    )
VAR _previousClose =
    MAXX (
        FILTER (
            'Table',
            'Table'[Ticker] = EARLIER ( 'Table'[Ticker] )
                && 'Table'[Date] = _previousDate
        ),
        'Table'[Close]
    )
RETURN
    _previousClose

With the second method, you need to have a [Work Day] column in Date table to mark if a date is a working day.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.