Create a Readmission Formula that only brings back accts where there was a readmission w/ in 90 days

[jonnyA]

Create a Readmission Formula that only brings back accts where there was a readmission w/ in 90 days.  Exclude accounts where admission and discharge 

For example, in the screen shot, all accounts, other than the yellow highlighted rows (Because the admittance and remit codes are greater than 90 days), should be in the matrix.

I need a formula, that will bring backl those results.  Thank you for your help in advance!

Admission Procedure Codes = 99221, 99222, 99223

Discharge Procedure Codes = 99238, 99239

Fields ...

• ABC_Discharge Dataset
• Patient Name
• DOS
• Procedure Code

[richbenmintz · Accepted solution]

Hi @jonnyA ,

I have broken the issue into two steps:

1. Create a calc column that gets the correlated admission and discharge dates for each row

2. Create a calc column that gets the absolute value of the date diff

3. filter the table by the calculated date difference

calc below:

admission_discharge_date = 
var _proc_code = 'ABC_Discharge Dataset'[Proc_Code]
var _dos = 'ABC_Discharge Dataset'[DOS]
var _patient_f_name = 'ABC_Discharge Dataset'[Patient_First_name]
var _patient_l_name = 'ABC_Discharge Dataset'[Patient_Last_Name]
return 
SWITCH(true(),
    _proc_code in {99238, 99239}, 
    CALCULATE(MAX('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99221, 99222, 99223})),
    _proc_code in {99221, 99222, 99223},
     CALCULATE(min('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]> _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99238, 99239})),
blank())
// CALCULATE(MAX('ABC_Discharge Dataset'[DOS]), FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos && _proc_code in {99221, 99222, 99223})))

admit_discharge_delta = ABS(DATEDIFF('ABC_Discharge Dataset'[DOS], 'ABC_Discharge Dataset'[admission_discharge_date],DAY))

Results in the following:

Non FIltered:

Filtered:

Attched please find a sample pbix.

hope this helps!

[jonnyA]

Does anyone know how I can fix this measure??

[jonnyA]

@v-yalanwu-msft ,

Hello, and thank you for your help with the file you sent me, very helpful!

I cannot get the "Flag" measure to work on my end.  The formula has no errors from what I can tell, but when I add the formula to my matrix, the data goes blank.

Any suggestions on how to fix?

[v-yalanwu-msft · Accepted solution]

Hi, @jonnyA ;

You could try to create a flag measure.

flag = 
VAR _next=
IF(
    MAX([Proc_Code]) in {99238, 99239}, 
    MAXX(FILTER(ALL(ABC_Discharge),[Patient_First_name]=MAX([Patient_First_name])&&[Patient_Last_Name]=MAX([Patient_Last_Name])
      &&[DOS]< MAX('ABC_Discharge'[DOS])&&[Proc_Code] in {99221, 99222, 99223}),[DOS]),
    MINX(FILTER(ALL(ABC_Discharge),[Patient_First_name]=MAX([Patient_First_name])&&[Patient_Last_Name]=MAX([Patient_Last_Name])
      &&[DOS]>MAX('ABC_Discharge'[DOS])&&[Proc_Code] in {99238, 99239}),[DOS]))
VAR _DIFF=IF(MAX([Proc_Code]) IN {99238,99239},DATEDIFF(_next,MAX([DOS]),DAY),DATEDIFF(MAX([DOS]),_next,DAY))
RETURN IF(_DIFF<90,1,0)

Then apply it into filter:

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fowmy]

@jonnyA 

As per your data, there are three rows that exceed 90 days. Do you want to create a new table that shows only those three rows that I highlighted in yellow or you need a measure?

[jonnyA]

@fowny,

I dont know why you are seeing 3 and i see 2 tho?

Keep in mind Admission codes vs Discharge.

So, if Admission Proc Code Date of Sevice is over 90 days from Discharge Procedure Code then those patients should be left off. 

[jonnyA]

@Fowmy 

I need a measure.  Thank you!

[richbenmintz · Accepted solution]

Hi @jonnyA ,

I have broken the issue into two steps:

1. Create a calc column that gets the correlated admission and discharge dates for each row

2. Create a calc column that gets the absolute value of the date diff

3. filter the table by the calculated date difference

calc below:

admission_discharge_date = 
var _proc_code = 'ABC_Discharge Dataset'[Proc_Code]
var _dos = 'ABC_Discharge Dataset'[DOS]
var _patient_f_name = 'ABC_Discharge Dataset'[Patient_First_name]
var _patient_l_name = 'ABC_Discharge Dataset'[Patient_Last_Name]
return 
SWITCH(true(),
    _proc_code in {99238, 99239}, 
    CALCULATE(MAX('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99221, 99222, 99223})),
    _proc_code in {99221, 99222, 99223},
     CALCULATE(min('ABC_Discharge Dataset'[DOS]),FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]> _dos&& 'ABC_Discharge Dataset'[Proc_Code] in {99238, 99239})),
blank())
// CALCULATE(MAX('ABC_Discharge Dataset'[DOS]), FILTER(all('ABC_Discharge Dataset'), 'ABC_Discharge Dataset'[Patient_First_name]= _patient_f_name && 'ABC_Discharge Dataset'[Patient_Last_Name] = _patient_l_name &&'ABC_Discharge Dataset'[DOS]< _dos && _proc_code in {99221, 99222, 99223})))

admit_discharge_delta = ABS(DATEDIFF('ABC_Discharge Dataset'[DOS], 'ABC_Discharge Dataset'[admission_discharge_date],DAY))

Results in the following:

Non FIltered:

Filtered:

Attched please find a sample pbix.

hope this helps!