Percent deviation from previous value

[mgcchkn]

Index	Name	Test Date	Test Status	Test Duration	Current Test	Previous Test
331891	John	8/10/2022	APPROVED	24	200
331892	John	5/4/2022	APPROVED	24	71
331893	John	7/6/2022	APPROVED	24	1066
331894	John	6/17/2022	APPROVED	24	228
331895	John	9/17/2020	APPROVED	24	113

Above is my sample data.

I'm trying to figure out percentage of deviation from previous test.  I can't even figure out how to get the previous test to show up in the Previous Test column.  Ultimately, there would be a 8th column that had the percentage of deviation.

As you can see, the index column is missing some numbers because of filtering.  So these tests are for a particular "student" which is filtered via a drill through with a measure called "selected_student."  (selected_student = var selected_student = SELECTEDVALUE(student[name]) return "Test History for Student: " & UNICHAR(10) & selected_student)

Thanks in advance! I've been working on this and googling it for 2 days and can't figure it out.

[mgcchkn]

I figured it out, but it takes forever for it to load now.  It takes between 60-90 seconds.

[v-easonf-msft · Accepted solution]

Hi， @mgcchkn 

Take a try formula like below:

Previous Test = 
VAR _PreviousTestDate =
    CALCULATE (
        MAX ( Student[Test Date] ),
        FILTER (
            ALL ( Student ),
            Student[Name] = MAX ( Student[Name] )
                && Student[Test Date] < MAX ( Student[Test Date] )
        )
    )
RETURN
    CALCULATE (
        MAX ( Student[Current Test] ),
        FILTER (
            ALL ( Student ),
            Student[Name] = MAX ( Student[Name] )
                && Student[Test Date] = _PreviousTestDate
        )
    )

Percenatge = DIVIDE(MAX(Student[Current Test])-[Previous Test],[Previous Test])

Best Regards,
Community Support Team _ Eason

[mgcchkn]

That worked great!  Thank you!!!