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

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!

 

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.