Forum Discussion
Responsive ResidentDAX - Calculating Previous Record from another Table
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
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] )
16 Replies
spandy34Thats fabulous - its worked thank you.
- spandy34
Yes by Unique Ref field
- spandy34
Yes by Unique Ref field
- spandy34
I get this error when I create the Previous Performance DAX in the Most Recent table
- 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])- spandy34
<ccon>A circular dependency was detected: Most Recent[Previous Performance], Most Recent[Diff in Per No Previous], Most Recent[Previous Performance].</ccon>
- 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 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
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
Community Champion
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] )