Forum Discussion
Need help with conditional data aggregation
My source data format (after unpivoting) looks like this -
PatientID | Status | Date
p234 | Active | 01-Oct-2018
p234 | Discharged | 12-Dec-2018
p234 | Dropped | 15-Feb-2019
p235 | Active | 11-Oct-2018
p235 | Discharged | 05-Jan-2019
p236 | Active | 17-Nov-2018
p236 | Discharged | 15-Jan-2019
p236 | Dropped | 18-Jan-2019
Basically, each patient goes through a journey of becoming active (in a support program), discharged and then finally dropped. What I need to build is a report that answers this business question - how many Active/Discharged/Dropped patients do I have in any given month, Jan-2019?
The desired output should look like this -
Oct-2018 | Nov-2018 | Dec-2018 | Jan-2019 | Feb-2019
Active 2 | 3 | 2 | 0 | 0
Discharged 0 | 0 | 1 | 2 | 1
Dropped 0 | 0 | 0 | 1 | 2
Total 2 | 3 | 3 | 3 | 3
Explanation -
1. 2 patients (p234 & p235) were active in Oct-2018 and then p235 is active in Nov-2018. Therefore, total active patients in Oct-2018 and Nov-2018 are 2 and 3 respectively.
2. Patient p234 gets discharged in Dec-2018, therefore for Dec-2018 Active = 2, Discharged = 1.
3. Next, patient p235 gets discharged on 5-Jan-2019. p236 gets discharged on 15-Jan-2019 and subsequently dropped on 18-Jan-2019. Therefore, for Jan-2019 Active = 0, Discharged = 2, Dropped = 1. Note - if a patient's status change during the same month, for example p236 gets discharged and dropped in Jan-2019, the patient will be counted as dropped. Similarly, for instance, if a patients is active and then discharged during the same month then the patient will be counted as discharged for that month.
Please help! Please download the PBIX file from here. I have included the output as a table named Desired Output which I manually typed :).
Thanks
Hi,
This is the result i got.
9 Replies
Hi,
This is the result i get. I do not quite agree with yoru results in Feb 2019. I think for Feb 2019, discharged should be 0 and dropped should be 1. I have shown patient ID's there to prove my point. Are you OK with this result?
Hi Ashish_Mathur,
I agree with your result till Jan-2019 (there were some mistakes in my previous Desired Output table). So, for Jan-2019 - Active = 0, Discharged = 2 (p234 & p235), Dropped = 1 (p236) and this is correct. But in Feb-2019, p234 gets dropped so will get added to Dropped count for Feb-2019. And therefore for Feb-2019 - Active = 0, Discharged = 1 (p235 is carried forward from Jan-2019) and Dropped = 2 (p236 & p234). I hope this makes sense.
I have corrected the PBIX file, link is here.
Thanks
Hi,
This is the result i got.
