Late reply to an old post, but in case anyone else stumbles across this...
Assumptions:- Your data is in a table called Data in PowerPivot
- Your lookup table is called CodeData in PowerPivot
- A relationship has been created in the data model between your data table (Data) and your lookup table (CodeData) based on the Code field
- Readmission time frame is 30 days
1. Create a measure (calculated field in Excel 2013): M_DISCHARGE_COUNT
2. Create a calculated column (in the PowerPivot table): C_DISCH_DATE+30
This is a helper column used for the next calculation that adds 30 days to the discharge date. You could simply use this formula in the next calculation instead of creating a column, but I prefer to keep it separated to visualize what is going on.
3. Create a calculated column (in the PowerPivot table): C_NUMBER_OF_READMISSIONS
This calculates the number of readmissions within a 30 day period for each row of data. If you have 4 admissions within 30 days for the same patient, the output would be:
1/1/2015 3
1/3/2015 2
1/5/2015 1
1/7/2015 null
4. Create a calculated column (in the PowerPivot table): C_READMISSION_COUNTER
Since the previous calculation gives us total readmissions and duplicates values, we need a helper column to drag into the pivot table. We will also address the Heart Failure requirement.
5. Create a pivot table in Excel, drag in MRN and FIN as your row criteria and drag C_READMISSION_COUNTER as your values (or create an additional measure summing C_READMISSION_COUNTER to avoid implicit calculations). Any row in your pivot table that shows a 1 means there is a subsequent admission within 30 days for all heart failure admissions.
I've tried to do this with strictly measures (calculated fields in Excel 2013) and not columns, but can't find an efficient way to do it. The key is the EARLIER() function. If there are any questions feel free to respond (or if there is a better way). Hope it helps!
Bookmarks