Forum Discussion
Percent deviation from previous value
| 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.
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
3 Replies
I figured it out, but it takes forever for it to load now. It takes between 60-90 seconds.
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 _ EasonThat worked great! Thank you!!!
