Difference Between Two Related Rows in a Table

[samdep]

Hi Community,

I have a table, similar to the below, where I'd like to show the difference between the manager's rating of the direct report and the direct report's rating of themselves. I'd like to create a measure that looks at the difference between the manager's rating and the direct report's rating, and from there, add a flag within the cell if the difference between the two ratings is greater than or equal to 2.

Previously, I had used the following DAX measure and it worked fine, but now it's throwing an error with 'EARLIER'. 

Judgment Variance = 
VAR _MAX = MAXX(FILTER('Assessments', 'Assessment'[Index] < EARLIER([Index]) && 'Assessment{'Manager & Direct Report'] = EARLIER('Assessment{'Manager & Direct Report'])), [Index]

VAR _RATING = 'Assessment'[Judgment] - MAXX(FILTER('Assessments', 'Assessment'[Index] < EARLIER([Index]) && 'Assessment{'Manager & Direct Report'] = EARLIER('Assessment{'Manager & Direct Report'])), 'Assessment'[Judgment]

RETURN

IF(_RATING = 'Assessment'[Judgment], BLANK(),
IF(_RATING = 0, BLANK(), ABS(_RATING))
)

Appreciate any guidance!

Date	Index	Name	Type	Manager	Mgr &  Report	Judgment Rating	Judgment Variance
3/31/2023 4:00pm	1	John	Assessment of Report		John-Sally	5	1
4/2/2023 5:00pm	2	Sally	Self-Assessment	John	John-Sally	4

[Anonymous · Accepted solution]

Hi @samdep ,

First, please make sure that what you are creating is a calculated column not a measure, then update its formula as below to get the expected result:

Judgment Variance = 
VAR _nextindex =
    MINX (
        FILTER (
            'Assessment',
            'Assessment'[Index] > EARLIER ( [Index] )
                && 'Assessment'[Manager & Direct Report] = 'Assessment'[Manager & Direct Report]
        ),
        [Index]
    )
VAR _nextrating =
    MAXX (
        FILTER (
            'Assessment',
            'Assessment'[Index] = _nextindex
                && 'Assessment'[Manager & Direct Report] = EARLIER ( 'Assessment'[Manager & Direct Report] )
        ),
        [Judgment]
    )
RETURN
    IF (
        ISBLANK ( _nextrating )
            || _nextrating = 'Assessment'[Judgment],
        BLANK (),
        ABS ( 'Assessment'[Judgment] - _nextrating )
    )

Best Regards

[amitchandak]

@samdep , try like

Judgment Variance =
VAR _MAX = MAXX(FILTER('Assessments', 'Assessment'[Index] < EARLIER([Index]) && 'Assessment'[Manager & Direct Report] = EARLIER('Assessment'[Manager & Direct Report])), 'Assessment'[Index])

VAR _RATING = 'Assessment'[Judgment] - MAXX(FILTER('Assessments', 'Assessment'[Index] =_max && 'Assessment'[Manager & Direct Report] = EARLIER('Assessment'[Manager & Direct Report])), 'Assessment'[Judgment])

RETURN

IF(_RATING = 'Assessment'[Judgment], BLANK(),
IF(_RATING = 0, BLANK(), ABS(_RATING))
)

Power BI DAX- Earlier, I should have known Earlier: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8