How to Calculate Assessment Delta Outcome

Tristan Keelan
Apr 5, 2021 1:09:37 AM


HubSpot Video


This video tutorial outlines how to organize your assessment data in Power BI so that you can report on the assessment delta, or, the numerical change your agency made for your client in your chosen scale.  This is one of the most important outcome measurements you can track to demonstrate your agency's effectiveness for a few reasons.

First, in the new world of value based payments, insurance payers only have claims data to calculate outcomes, which results in a lot of volume-based and time-based measures.  This allows your agency to bring meaningful data to they table that they will be interested in but won't have on their own.

Second, it works with any scale.  The same formula's and reporting strategy works for a DLA-20 in mental health as it does for the NCFAS scale in a wraparound program.  As long as the scale can produce a single summary score and all questions have a consistent higher is better or lower is better direction the strategy will work.  In some cases like the SDQ, some questions are higher is better and some are lower is better.  Its important to separate them into two analytical sets.  Just repeat the steps with totals for the different question groupings. 

Here are just a few assessments that come to mind:

Third, you can apply this at the case management level for treatment planning and discharge criteria.  Individual providers can track the assessment scores throughout treatment to help drive interventions.  At the same time it can help solve that pesky question "how do we define 'Successful Discharge' again?" You can instead decide as a program that a successful discharge criteria might include an assessment delta improvement of some amount, among other factors.

Last, you will maintain a meaningful outcome for disengaged clients.  If you encounter AWOL in a residential program, or a client who simply stopped responding to outpatient services, as long as they had at least 2 assessments you have a record of the change that was made prior to the disengagement.  While you may not have achieved the desired post program goal, you may have made an improvement during that length of stay before the separation event.

Formulas Used

Version # = VAR a = 'Assessment Scores - Sample'[Assessment Date] VAR b = 'Assessment Scores - Sample'[Episode of Care] RETURN CALCULATE ( RANK.EQ (a, 'Assessment Scores - Sample'[Assessment Date],ASC), FILTER ( ALL('Assessment Scores - Sample'),'Assessment Scores - Sample'[Episode of Care] = b ) )
Assessment Score - First = CALCULATE( AVERAGE( 'Assessment Scores - Sample'[Score]), FIRSTNONBLANK('Assessment Scores - Sample'[Version #], TRUE()))
Assessment Score - Last = CALCULATE( AVERAGE('Assessment Scores - Sample'[Score]), LASTNONBLANK( 'Assessment Scores - Sample'[Version #], TRUE()))
Assessment Delta = [Assessment Score - Last]-[Assessment Score - First]
Average Assessment Delta = AVERAGEX(SUMMARIZE('Assessment Scores - Sample','Assessment Scores - Sample'[Episode of Care],"toaverage",[Assessment Delta]),[Assessment Delta])
*Reminder that formulas will have to use fields from your own data set. eg 'Your Table'[Your Field]

If you have any questions post them in the comments below.

For more video tutorials about reporting Behavioral and Mental Health Data: Click Here >>