DAX - Calculating Previous Record from another Table

[spandy34]

I have two tables - 1) Performance Table that contains all performance records and 2) Most Recent Table that contains the last and most recent record for each Performance Indicator Measure  

Within the Most Recent table I have a field called Previous Performance.  Within this field I want the value of the Previous Performance within the Performance Table to be returned of the last but one record

example below 

Measure SBC/998 – Performance Table has 3 entries for this Unique Ref – the most recent (24/04/2023) = 5050 and the second last entry is 24/02/2023 = 3800.

I want the formula within the Most Recent Table ‘Previous Performance’ to return the second last Performance Table ‘Current Performance’ entry which is 3800

Can someone please help me.  I have tried but am getting mixed up with the tables and getting error

@tamerj1 @amitchandak @ribisht17 @danextian @goncalogeraldes @macmy034 

[tamerj1 · Accepted solution]

@spandy34 

Please try

Previous Performance =
MAXX (
    TOPN (
        1,
        TOPN (
            2,
            RELATEDTABLE ( 'performance table' ),
            'performance table'[Date Input]
        ),
        'performance table'[Date Input], ASC
    ),
    'performance table'[Current Performance]
)

[spandy34]

Thats fabulous - its worked thank you.

[spandy34]

When I put your recommended DAX below:-

Previous Performance =
MAXX (
TOPN (
1,
TOPN (
2,
RELATEDTABLE ( 'performance table' ),
'performance table'[Date Input]
),
'performance table'[Date Input], ASC
),
'performance table'[Current Performance]
)

I get the error in the picutre below:-

[spandy34]

I'm so sorry for the confusion but as I was trying to work through this I realised the Most Recent and Performance Table and not related tables. 
Even though the values in Previous Performance work, do you think I'm getting the circular reference because they are not toward.  Here was the original code for the column you suggested .

Previous Performance =

MAXX (

TOPN (

1,

TOPN (

2,

RELATEDTABLE ( 'performance table' ),

'performance table'[Date Input]

),

'performance table'[Date Input], ASC

),

'performance table'[Current Performance]

)

[tamerj1 · Accepted solution]

@spandy34 

It could happen for complex models. Let's avoid context transition and just use

Previous Performance =
MAXX (
    TOPN (
        1,
        TOPN (
            2,
            FILTER (
                'performance table',
                'performance table'[Unique Redlf] = 'Most Recent Table'[Unique Ref]
            ),
            'performance table'[Date Input]
        ),
        'performance table'[Date Input], ASC
    ),
    'performance table'[Current Performance]
)

[tamerj1]

@spandy34 

This is supposed to be a calculated column in the 'Most Recent' table

[spandy34]

I there I have started again and used your code so I don't get circular dependencies and it has worked . Thank you very much for your help 

[spandy34]

The Diff in Per No Previous column the error refers to is 

Diff in Per No Previous = if(isblank([Previous Performance]),0,[Current Performance]-[Previous Performance])

[tamerj1]

@spandy34 

Then you need to present the complete picture 

[tamerj1]

@spandy34 

Sorry I didn't understand. Which error?

[spandy34]

<ccon>A circular dependency was detected: Most Recent[Previous Performance], Most Recent[Diff in Per No Previous], Most Recent[Previous Performance].</ccon>

[spandy34]

Yes by Unique Ref field 

[tamerj1 · Accepted solution]

@spandy34 

Please try

Previous Performance =
MAXX (
    TOPN (
        1,
        TOPN (
            2,
            RELATEDTABLE ( 'performance table' ),
            'performance table'[Date Input]
        ),
        'performance table'[Date Input], ASC
    ),
    'performance table'[Current Performance]
)

[spandy34]

Thank you this has worked 

[spandy34]

I get this error when I create the Previous Performance DAX in the Most Recent table

[tamerj1]

Hi @spandy34 

are they connected?

[spandy34]

Yes by Unique Ref field